Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up duplicate amounts with unique batch numbers
I have two lists in separate spreadsheets, illustrated below. How do I find
a match using the Amount1 field as the key, and match to the second list with the correct batch number? Both lists are unequal in the number of records. Thanks for any help. Amount Batch Number Amount1 (key) 3.45 8123953001 3.45 3.88 8123971001 3.88 4.40 8123872001 4.40 4.43 8123A42001 4.43 4.45 8123970001 4.45 5.03 8123681001 5.03 5.15 8123839001 5.15 5.49 8123571001 5.49 5.49 8123803001 5.49 5.49 8123804001 5.49 5.49 8123983001 5.49 5.49 8123A52001 5.49 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up duplicate amounts with unique batch numbers
assuming your data are in Sheet 1 and the 'Amount 1 (key' value are in A2
down on another sheet put this in b2 and drag down VLOOKUP(A2,Sheet1!$A$2:$B$13,2,FALSE) Mike "JHL" wrote: I have two lists in separate spreadsheets, illustrated below. How do I find a match using the Amount1 field as the key, and match to the second list with the correct batch number? Both lists are unequal in the number of records. Thanks for any help. Amount Batch Number Amount1 (key) 3.45 8123953001 3.45 3.88 8123971001 3.88 4.40 8123872001 4.40 4.43 8123A42001 4.43 4.45 8123970001 4.45 5.03 8123681001 5.03 5.15 8123839001 5.15 5.49 8123571001 5.49 5.49 8123803001 5.49 5.49 8123804001 5.49 5.49 8123983001 5.49 5.49 8123A52001 5.49 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up duplicate amounts with unique batch numbers
Mike H, thanks for the reply. However, I had already tried this. The
problem is it doesn't recognize the other batches with the duplicate amount for 5.49. Vlookup will only match the first batch and repeat that reference for the other 5.49 items. "Mike H" wrote: assuming your data are in Sheet 1 and the 'Amount 1 (key' value are in A2 down on another sheet put this in b2 and drag down VLOOKUP(A2,Sheet1!$A$2:$B$13,2,FALSE) Mike "JHL" wrote: I have two lists in separate spreadsheets, illustrated below. How do I find a match using the Amount1 field as the key, and match to the second list with the correct batch number? Both lists are unequal in the number of records. Thanks for any help. Amount Batch Number Amount1 (key) 3.45 8123953001 3.45 3.88 8123971001 3.88 4.40 8123872001 4.40 4.43 8123A42001 4.43 4.45 8123970001 4.45 5.03 8123681001 5.03 5.15 8123839001 5.15 5.49 8123571001 5.49 5.49 8123803001 5.49 5.49 8123804001 5.49 5.49 8123983001 5.49 5.49 8123A52001 5.49 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up duplicate amounts with unique batch numbers
Hi,
The Lookup value has to be from a list of unique data. When your lookup value is 5.49, which batch number do you actually want returned? Also, something I didn't understand from your original post, You said you had 2 lists in separate spreadsheets, but your data is arranged in 3 lists (Amount, Batch Number, Amount 1) Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Duplicate Data to obtain Unique record | Excel Worksheet Functions | |||
Duplicate and unique items in 2 lists | Excel Discussion (Misc queries) | |||
how to get word amounts from numbers in cels | Excel Discussion (Misc queries) | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
In column A I have duplicate records. How do I tag an unique reco. | Excel Discussion (Misc queries) |