[SOLVED] lookup and list items with same value & related info

[dradenpoel]

Hey all,

I am making a spreadsheet for making internal coupons.

File can be found here:
https://drive.google.com/file/d/1Q8qpeDQcD9S7dSXDnW8I_ZoMNMGX3Ma5/view?usp=sharing

In short;
I have cols that contain info about what's moved, in one of the cols I put a coupon number/name.
In another tab of the file I would like to list all info that has the same coupon number/name. So far I am trying on the same tab ("ingave")

I got so far as to find out what rows have the same coupon number/name using match and index.
see col R on tab "ingave", match uses S6 as searchterm to give result in R9, R10-12 use index and the row found in R8 to say j or n depending on whether or not the coupon number/name is the same as the result found in R9.

I know how to find all info using index/match, but am puzzled about how and where to start and how to compile the formulas with the info I got so far.

Any help is welcome

Kind regards,
Ben

(edit: first forgot to attach the file, then couldn't find how to attach a file, shared through google drive link)

 

[Ralston18]

Terminology question:

Coupon: is that a document of some sort more like a shipping invoice? Versus "coupon" as used for giving a product discount on something?

Just looking at context of your spreadsheet seems to indicate that. Not sure about some of the column headers so feel free to correct and explain further as necessary.

Is this a new spreadsheet just being created?

The difficulties you are having are all too common with the use of a spreadsheet when a database is much more applicable and more appropriate.

And the word database tends to be intimidating to many people which is why so many people try to use a spreadsheet.

And all the more likely to become increasingly cumbersome as the spreadsheet grows with respect to rows and columns. Or other workflow changes occur.

If you are using Excel (.xlsx) then Access (database) would be suitable choice.

For the most part do not let "database" become a barrier to your efforts and requirements.

Once past a few very basic concepts the database will be easier to use and maintain. And be scalable as well.

One concept is "normalization" which can be very overwhelming. But for most applications, normalization is very straight forward.

Here are three links:

https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

https://www.mygreatlearning.com/blog/ms-access-tutorial/

https://sirinc2.org/a16cat/2020 Presentations/Nov 2020 Introduction to Access.pdf

You can easily find other similar links on the topic.

Use the example in the first link (or other links that you find) as necessary to "normalize" the data that you need to collect, match, compare, etc..

Once that is done then use the Access wizards to create your tables and database.

May take a few tries but it should all fall into place very quickly.

After that then you will be able to build reports, queries, and forms that will further your requirements.

Note: For the most part existing spreadsheet data can either be imported into a database or linked to a database for access and further data manipulation. Point being that existing information in a spreadsheet does not necessary have to be redone or lost/given up in some manner.

Just my thoughts on the matter.

 

[dradenpoel]

Terminology question:

Coupon: is that a document of some sort more like a shipping invoice? Versus "coupon" as used for giving a product discount on something?

Just looking at context of your spreadsheet seems to indicate that. Not sure about some of the column headers so feel free to correct and explain further as necessary.

Is this a new spreadsheet just being created?

The difficulties you are having are all too common with the use of a spreadsheet when a database is much more applicable and more appropriate.

And the word database tends to be intimidating to many people which is why so many people try to use a spreadsheet.

And all the more likely to become increasingly cumbersome as the spreadsheet grows with respect to rows and columns. Or other workflow changes occur.

If you are using Excel (.xlsx) then Access (database) would be suitable choice.

For the most part do not let "database" become a barrier to your efforts and requirements.

Once past a few very basic concepts the database will be easier to use and maintain. And be scalable as well.

One concept is "normalization" which can be very overwhelming. But for most applications, normalization is very straight forward.

Here are three links:

https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

https://www.mygreatlearning.com/blog/ms-access-tutorial/

https://sirinc2.org/a16cat/2020 Presentations/Nov 2020 Introduction to Access.pdf

You can easily find other similar links on the topic.

Use the example in the first link (or other links that you find) as necessary to "normalize" the data that you need to collect, match, compare, etc..

Once that is done then use the Access wizards to create your tables and database.

May take a few tries but it should all fall into place very quickly.

After that then you will be able to build reports, queries, and forms that will further your requirements.

Note: For the most part existing spreadsheet data can either be imported into a database or linked to a database for access and further data manipulation. Point being that existing information in a spreadsheet does not necessary have to be redone or lost/given up in some manner.

Just my thoughts on the matter.

Hey Ralston18,

Though you are completely right, a database would be in place here, I simply don't have the time to get into studying how and what with access...

I am merely trying to simplify the creation of the invoices (better word than coupon) within an existing excel setup, now there is being used copy paste, which is for the lesser computergods not always a simple task to do so. "Where do I copy what?", "How do I paste it?" And that is without trying not to mess up the layout.

Bringing it back to filling in an invoice number would already be great, maybe, in time, if I have time, I will look into access, but for now, compiling a formula or set of formulas in a given range of cells seems less time consuming.

Kind regards,
Ben

 

[dradenpoel]

There is a simple solution, I found now. Using the actual row result instead of a "j" and "n". Then compiling where to look with the indirect formula (since I know where to look no complicated formula is needed)

The updated file can be found here
https://drive.google.com/file/d/11IUEa5i7BDEWY9uhzt8yKvOzJ8rmO_Zw/view?usp=sharing

 

[Ralston18]

Understood.

I am reminded of the saying "When you are up to your neck in alligators it is hard to remember that the original purpose was to drain the swamp".

Glad you worked it out but try to find some time with Access to make your work easier. Eventually that spreadsheet will get cumbersome and even more administratively challenging. Especially if other people are using it or trying to...... Cutting and pasting is fraught with problems as you obviously know all too well.

= = = =

An invoice is simply a report or form that can be generated with a simple button click. No "gods" required.

It appears that you are far enough along with Excel that Access will not be too difficult. Getting the basic tables in place is the key.

= = = =

Still, if you know someone who is familiar with Access then ask for help.

Probably would take only a few hours work to create a very basic and functional database to manage the invoice data and processes.

Make a copy of the spreadsheet and use that copy as a data source for Access. Either directly or by data import to an Access table.

How to do that:

https://www.lifewire.com/convert-excel-spreadsheet-access-2013-database-1019967

(And you can find many other similar links.)

Start simple, use the Wizards, do things manually at first and then work on macros, menus, etc. to automate common actions.

If things go astray, just go back as necessary and revise. May take a few "do over's" but all will become easier very quickly.