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)?

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)?

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)?