How do I retrieve data using duplicated values?

[rhug]

Hello all,

I have been reading some of the post in this forum for a while now, and have managed to glean some very useful info from it and have managed to adapt it to my needs. For that I would like to offer some direct gratitude for your indirect assistance thus far. That said, I am a relative newbie when it comes to using anything more than basic Excel formulas, so would request that you accept my apologies in advance, as I may very well struggle with some of the concepts and termanologies.

The current problem, for which I am struggling to find an answer is as follows:

I have a spread sheet that consists of sales order information (sheet 1), and on another sheet I have created an invoice/sales receipt (sheet 2), that I want to auto populate from the sales order information using the order number as the prerequisite.

On (sheet 1), Column 'A' contains ascending order numbers that begin with text characters i.e. (A:1 = BB0001, A:2 = BB0002) and so on. When a customer places an order for multiple items, each additional item is entered on a new line but with the same order number. I have tried to use the 'Vlookup' function to obtain data from say column 4, but have discovered that, although perfect for single item orders, 'Vlookup' will only return the values for the first instance, or on the first line that contains the order number it is looking for.

So my query therefore is this; Is it possible to create a formula that will return the values in say column 4, for each and every line that contains an occurrence of the order number specified.

Any assistance and/or comments would be greatly appreciated.

Thank you again in advance !!

[azumi]

You have the sample workbook? upload it in this thread

Azumi

[Pete_UK]

What you need to do is establish a unique reference for each record, but to link that reference to the order number. One way of doing that is to insert a new column A (so that you can use VLOOKUP), and then in A1 you can have a formula like this:

=IF(B1="","-",B1&"_"&COUNTIF(B$1:B1,B1))

Then you can copy this down way beyond the data that you currently have (the hyphens will show you where you have copied it to) so that you can cope with new data being added. You can hide this column if you want to, so that the sheet looks the same as before.

Then in your VLOOKUP formula you have to address a larger table so that you include the new column A, and instead of looking up a particular order number, you have to tag on that sequential count that has now been set up in the new column A. So, assuming the order number that you are looking for is in cell A1 of the Invoice sheet, your formula will be something like this:

=IFERROR(VLOOKUP($A$1&"_"&ROWS($1:1),Orders!A:E,5,0),"")

and this can now be copied down until you start to get blanks, and it will return successive records for that order number from the fourth column of your (old) database - actually column E in the new one.

Hope this helps.

Pete

[rhug]

Thank you for your reply Azumi. I think I may have found a solution from Pete in the next thread, although please keep an eye on me while I attempt to make sense of it and try it out. I may yet call upon your expertise.

Thanks again,

Rob

[rhug]

What you need to do is establish a unique reference for each record, but to link that reference to the order number. One way of doing that is to insert a new column A (so that you can use VLOOKUP), and then in A1 you can have a formula like this:

=IF(B1="","-",B1&"_"&COUNTIF(B$1:B1,B1))

Then you can copy this down way beyond the data that you currently have (the hyphens will show you where you have copied it to) so that you can cope with new data being added. You can hide this column if you want to, so that the sheet looks the same as before.

Then in your VLOOKUP formula you have to address a larger table so that you include the new column A, and instead of looking up a particular order number, you have to tag on that sequential count that has now been set up in the new column A. So, assuming the order number that you are looking for is in cell A1 of the Invoice sheet, your formula will be something like this:

=IFERROR(VLOOKUP($A$1&"_"&ROWS($1:1),Orders!A:E,5,0),"")

and this can now be copied down until you start to get blanks, and it will return successive records for that order number from the fourth column of your (old) database - actually column E in the new one.

Hope this helps.

Pete

Thank you Pete for you detailed input.

I have been looking at the problem for a few days now, and yes, I have noted the steps you have suggested on other threads and tutorials, but until now had not tied them together to create a workable solution. I can recognise some of the functions you have used, although not all, and I think I get the general jist of what to do, so I will have a play with it and let you know how I get on.

As asked of Azumi, please keep an eye on me, as it is quite likely that I may have the need to request further assistance.

With appreciation,

Rob

[Pete_UK]

Well, as we have both subscribed to the thread, we will get email notifications if there is another new post.

The first formula just adds a unique sequential number to the code that you have in (the new) column B. As these are unique, it means that VLOOKUP can pick them individually, as long as there is a sequential number in the lookup value - that is what the ROWS($1:1) term does, as when it is copied down it produces sequential numbers on successive rows.

In future it would help if you attached a sample workbook, to better illustrate what you want to achieve.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[rhug]

Thanks Pete for the extended explanatory. Thank you also for the user info, I will make good use if it ,no doubt, as time goes on. In fact I just did !!

Cheers

Rob

[rhug]

Hi Pete,

Just thought I would drop you a note to let you know that I figured it out. Despite the creation of an additional bald patch,, I applied your suggested formulas to a test sheet, and they work perfectly.

Thank you very much indeed for your assistance.

Cheers!

Rob

[Pete_UK]

You're welcome, Bob - thanks for feeding back.

Pete