Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
I need some help, and I am fairly new at this. I have a spreadsheet we fill
out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled in), 3rd is First name (filled in), forth is Dollars (filled in). I have a master document with the ID#, Last name, First name, all filled in with data. I want to compare names in the first document with the master document and take the ID# from the master and have that entered into the blank ID# column in the first document. I hope this makes sense... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
Assume the master data is in Sheet1, cols A to C, viz:
ID#, Last name, First name In the other sheet where col A (ID#) is blank, assuming Last name, First name data is running in A2:B2 down In A2, normal ENTER: =INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0)) Adapt the Sheet1 ranges to suit the extent of your actuals -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Western_man" wrote: I need some help, and I am fairly new at this. I have a spreadsheet we fill out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled in), 3rd is First name (filled in), forth is Dollars (filled in). I have a master document with the ID#, Last name, First name, all filled in with data. I want to compare names in the first document with the master document and take the ID# from the master and have that entered into the blank ID# column in the first document. I hope this makes sense... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
Typo:
.. assuming Last name, First name data is running in A2:B2 down "in A2:B2 down" should read: in B2:C2 down -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
Wow... that works great, thank you very much
I can follow the formula understanding now, but not sure why you use the * (multiply) on the last statement... anywho you've saved me weeks of time "Max" wrote: Assume the master data is in Sheet1, cols A to C, viz: ID#, Last name, First name In the other sheet where col A (ID#) is blank, assuming Last name, First name data is running in A2:B2 down In A2, normal ENTER: =INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0)) Adapt the Sheet1 ranges to suit the extent of your actuals -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Western_man" wrote: I need some help, and I am fairly new at this. I have a spreadsheet we fill out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled in), 3rd is First name (filled in), forth is Dollars (filled in). I have a master document with the ID#, Last name, First name, all filled in with data. I want to compare names in the first document with the master document and take the ID# from the master and have that entered into the blank ID# column in the first document. I hope this makes sense... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
Wow... thank you so very much, You have saved me so much time. This formula
works perfect, the only question I have would be why the * (multiply) for the last statement? Thanks again "Max" wrote: Assume the master data is in Sheet1, cols A to C, viz: ID#, Last name, First name In the other sheet where col A (ID#) is blank, assuming Last name, First name data is running in A2:B2 down In A2, normal ENTER: =INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0)) Adapt the Sheet1 ranges to suit the extent of your actuals -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Western_man" wrote: I need some help, and I am fairly new at this. I have a spreadsheet we fill out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled in), 3rd is First name (filled in), forth is Dollars (filled in). I have a master document with the ID#, Last name, First name, all filled in with data. I want to compare names in the first document with the master document and take the ID# from the master and have that entered into the blank ID# column in the first document. I hope this makes sense... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries
why the * (multiply) for the last statement?
The multiplication of the 2 conditions (identically sized ranges): (Sheet1!B$2:B$100=B2)*(Sheet1!C$2:C$100=C2) will produce a resultant array of ones/zeros depending on where the dual conditions are simultaneously satisfied or not, something like this: {0;0;0;1;0;0 ...0} MATCH(1, {0;0;0;1;0;0 ...0},0) then returns the relative position of the "1" in the resultant array, ie: 4 for the: INDEX(Sheet1!A$2:A$100 to return the 4th element within Sheet1!A$2:A$100 ie what's in A5 -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Western_man" wrote in message ... Wow... thank you so very much, You have saved me so much time. This formula works perfect, the only question I have would be why the * (multiply) for the last statement? Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Getting rid of not quite duplicate entries | Excel Worksheet Functions | |||
Duplicate entries | Excel Worksheet Functions |