Match / Index Multiple Columns and copy data

**SHS** · 04-07-2010, 06:29 AM

hi,

I've spent quite a few hours today trying to search for a solution to this and concluded it has something to do with Match and Index, but, can't for the life of me to get it all to work.

I don't think it strictly has to be a macro - a formula may work - but this is what i want to do:

In one sheet (called db) i have over 50,000 entries which relate to stock items seperated into style, colour, size and barcode.

In the other sheet i have a download of my stock database (called stck) which (after some manipulation) also has columns split into style, colour and size. This has about 700 items in it.

What i need to do is for all 700 items search through the db sheet and match style, colour and size, then copy the barcode from the db sheet to the stck sheet.

I know it ***should*** be simple to do, but, i can't for the life of me work it out, so any help / advise / solutions would be really really appreciated.

My excel sheet can be downloaded as a zip from here:
http://www.surplushardwaresupplies.c...xcel/addbc.zip

The upload utility keeps erroring on me for some reason, sorry...

Thanks

Mike

**ratcat** · 04-07-2010, 07:10 AM

G'day SHS,

Okay please do the following,

Go to your 'bc' sheet and at cell F2 and enter

=A2&B2&C2

then if your in the formula bar exit out by pressing enter then return back to cell F2 then press CTRL+C then hit the left arrow once then hold down the CTRL key and press the down arrow. Then press the right arrow once then hold down the CRTL+SHIFT keys and press the up arrow then press CTRL+V.

Now to your 'stck' sheet at cell H2 and format the cell 'Numbers' and now copy and paste the formula below

=IF(ISNA(INDEX(bc!$D$2:$F$50924,MATCH(E2&F2&G2,bc!$F$2:$F$50924,0),1)),"database not matching",INDEX(bc!$D$2:$F$50924,MATCH(E2&F2&G2,bc!$F$2:$F$50924,0),1))

now copy drag down.

HTH

Cheers

RC

**DonkeyOte** · 04-07-2010, 07:26 AM

I was going to suggest similar but using Binary Search INDEX/MATCH given the data on bc is already sorted appropriately.

I was going to suggest a concatenation on bc sheet of:

=$A2&"@"&$B2&"@"&$C2
copied down

Followed by summary calc of:

H2:
=LOOKUP(9.99E+307,CHOOSE({1,2},0,IF(VLOOKUP($E2&"@"&$F2&"@"&$G2,bc!$F$2:$F$50924,1)=$E2&"@"&$F2&"@"&$G2,INDEX(bc!$D$2:$D$50924,MATCH($E2&"@"&$F2&"@"&$G2,bc!$F$2:$F$50924)))))
copied down

the above should prove to be a lot quicker than conducting exact matches over such a vast range
(0 implies no match - a Custom Format can be used to have the 0's display however you want)

**SHS** · 04-07-2010, 08:46 AM

Perfect. Exactly what i needed, thank you very much.

I had to play around with the sizes in the main db as they had a space, but that only took a few minutes and it now all works. Just a shame my supplier didnt provide me with all the barcodes for all my items

**DonkeyOte** · 04-07-2010, 08:54 AM

Not sure which you opted for but I for one failed to add the SUBSTITUTE as I should have done to the concatenation formula such that it becomes:

=SUBSTITUTE($A2&"@"&$B2&"@"&$C2," ","")
copied down

The other formula remains as was...

I did a quick benchmark test between the exact match approach and the binary search ... using your file and on my machine (4GB RAM) the exact match takes around 4 secs to calculate (700 calcs) whereas the binary search takes around a hundreth of a second.

Though 4 seconds is not long it does prove the gains that can be had from sorting and using binary search methods as only a fraction of the transactions are reviewed (savings will increase the greater the range / no. of calcs)

EDIT: apologies for the edits - trying to word correctly such that it made sense.

**shahin** · 07-26-2010, 05:13 PM

Hello,

I have tried to use both of these methods for my data. The second method did not work for me, and I have just realized that the first method doesn't even work for the original dataset. If you compare the 2 methods using the original dataset, you'll see that a large number of the barcodes don't show up with the first method, whereas they do with the second method. This suggests to me that there is something wrong with the formula being used, which is unfortunate for me because the other formula doesn't seem to work for me. If someone could fix the 1st formula I would like to try to use it, otherwise I will post my data and perhaps someone can adapt the second formula to it.

thank you

**pike** · 07-27-2010, 03:30 AM

Hi shahin
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.