Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Cell Contents in 2 or More Columns
The title of this post doesn't quite capture what I'd love to be able to do.
I have a column of master data in Column A. These records comprise the entire set of data to be considered. In Column B, I import records that are a subset of the records in Column A. Is there a way to "sort" the data in Column B so that B2 = A2, B44=A44, etc. An example would be: A1 - "Record 1" B1 - "Record 1" A2 - "Record 2" B2 - " " (no record in the subset, so it's blank) A3 - "Record 3" B3 - "Record 3" A4 - "Record 4" B4 - "Record 4" A5 - "Record 5" B5 - no record in the subset, so again, it's blank While I'm on the subject, If the above actually can be done, if I had data in columns C and D that was related to data in Column B, could I sort B,C, and D columns as a group, but make the data in B my sort key (as I've dopne above)? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Cell Contents in 2 or More Columns
One way to achieve this ..
Assuming master key data in col A, subset key data in col B with ancillary subset data in cols C and D, all data from row1 down Put in E1: =IF(ISNA(MATCH($A1,$B:$B,0)),"",INDEX(B:B,MATCH($A 1,$B:$B,0))) Copy E1 to G1, fill down to the last row of data in col A. Cols E to G would return the results that you seek. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ConfusedNHouston" wrote: The title of this post doesn't quite capture what I'd love to be able to do. I have a column of master data in Column A. These records comprise the entire set of data to be considered. In Column B, I import records that are a subset of the records in Column A. Is there a way to "sort" the data in Column B so that B2 = A2, B44=A44, etc. An example would be: A1 - "Record 1" B1 - "Record 1" A2 - "Record 2" B2 - " " (no record in the subset, so it's blank) A3 - "Record 3" B3 - "Record 3" A4 - "Record 4" B4 - "Record 4" A5 - "Record 5" B5 - no record in the subset, so again, it's blank While I'm on the subject, If the above actually can be done, if I had data in columns C and D that was related to data in Column B, could I sort B,C, and D columns as a group, but make the data in B my sort key (as I've dopne above)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Cell Contents in 2 or More Columns
the simplest way is to actually put the imported data in a separate sheet
(Sheet2? starting column b) in b1 of Sheet1 enter =if(isna(vlookup($A1,'Sheet2'!$B:$D,column(),0),"" ,vlookup($A1,'Sheet2'!$B:$D,column(),0)) caopy and paste as far over and down as you need to check if any of the imported data does not have an existing match in A1 of sheet2 =countif('Sheet1'!A:A,B1) anything which is a zero will not have a reference "ConfusedNHouston" wrote: The title of this post doesn't quite capture what I'd love to be able to do. I have a column of master data in Column A. These records comprise the entire set of data to be considered. In Column B, I import records that are a subset of the records in Column A. Is there a way to "sort" the data in Column B so that B2 = A2, B44=A44, etc. An example would be: A1 - "Record 1" B1 - "Record 1" A2 - "Record 2" B2 - " " (no record in the subset, so it's blank) A3 - "Record 3" B3 - "Record 3" A4 - "Record 4" B4 - "Record 4" A5 - "Record 5" B5 - no record in the subset, so again, it's blank While I'm on the subject, If the above actually can be done, if I had data in columns C and D that was related to data in Column B, could I sort B,C, and D columns as a group, but make the data in B my sort key (as I've dopne above)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Field Contents | Excel Discussion (Misc queries) | |||
Combining Cell Contents - entire columns | Excel Worksheet Functions | |||
Comparing cell contents with different reference cells | Excel Worksheet Functions | |||
Comparing contents of two spreadsheets and outputting results to a | Excel Worksheet Functions | |||
Comparing Workbook contents | Excel Discussion (Misc queries) |