HELP HELP HELP HELP HELP

[PatrickL]

I have two worksheets. Both worksheets have data in column A, B, C, D, E, F,
and G. Column B has a list of with text and numbers(this is in both
worksheets). There are multiple duplicates in each worksheet. I need to
combine the two worksheets into one worksheet with no duplicates.

I also need to pull the data in the other columns over to the new worksheet.

I really need help with this. The boss is expecting a more efficient way of
doing this report.

 

[Harlan Grove]

PatrickL wrote...

I have two worksheets. Both worksheets have data in column A, B, C, D, E, F,
and G. Column B has a list of with text and numbers(this is in both
worksheets). There are multiple duplicates in each worksheet. I need to
combine the two worksheets into one worksheet with no duplicates.

I also need to pull the data in the other columns over to the new worksheet.

I really need help with this. The boss is expecting a more efficient way of
doing this report.

Use a database. That'd be much more efficient at performing the few
tasks you've described.

If that's not an option, then copy the col B data from each worksheet
into another worksheet's col B, with the 2nd worksheet's col B data
immediately below the 1st worksheet's col B data. If there's data in
cell B1 of this 3rd worksheet, insert a new row 1 and enter any text,
perhaps 'ANY', in cell B1. Copy cell B1 to, say, cell E1. Select all
data in col B from cell B1 down and run Data > Filter > Advanced
Filter. Your col B data is already the data range. Check the 'Copy to
another location' radio button, enter E1 as the 'Copy to' field, check
the 'Unique records only' check box, and click OK. This will put all
distinct values from col B in both original worksheets into col E of
this 3rd worksheet. Now fill in the other data from cols A, C-G from
the other worksheets in cols D, F-J in this 3rd worksheet. You haven't
provided sufficient details on the contents of these other cols in the
original 2 worksheets to be able to give any advice on how to collect
their data; however, if there are duplicates in col B, then the only
meaningful ways to collect data from the other columns is to calculate
descriptive statistics for them (COUNT [all], COUNT [distinct], SUM,
AVERAGE, MIN, MAX, etc.).

 

[PatrickL]

Thank You for your response.

The data in the other columns is as follows.

A B C D E
F G
Hunter HDMU458 SMITH 8/18/05 8/18/05 8/20/05
YES
Casa HDMU458 SMITH 8/18/05 8/18/05 8/20/05
YES

If column B matches in both lists I need the formula to check column A. If
column A has "Hunter" on one list and "Casa" on the other, I need to combine
them to read "Hun/Casa" in column A of the new sheet. If column A reads
"Hunter" and "Hunter" or "Casa" or "Casa" I need to have either the "Hunter"
or "Casa" if the column A corresponding to the matches in column B have the
same data in column A. The other data can be pulled over as is.

Does that make sense?

PatrickL wrote...

I have two worksheets. Both worksheets have data in column A, B, C, D, E, F,
and G. Column B has a list of with text and numbers(this is in both
worksheets). There are multiple duplicates in each worksheet. I need to
combine the two worksheets into one worksheet with no duplicates.

I also need to pull the data in the other columns over to the new worksheet.

I really need help with this. The boss is expecting a more efficient way of
doing this report.

Use a database. That'd be much more efficient at performing the few
tasks you've described.

If that's not an option, then copy the col B data from each worksheet
into another worksheet's col B, with the 2nd worksheet's col B data
immediately below the 1st worksheet's col B data. If there's data in
cell B1 of this 3rd worksheet, insert a new row 1 and enter any text,
perhaps 'ANY', in cell B1. Copy cell B1 to, say, cell E1. Select all
data in col B from cell B1 down and run Data > Filter > Advanced
Filter. Your col B data is already the data range. Check the 'Copy to
another location' radio button, enter E1 as the 'Copy to' field, check
the 'Unique records only' check box, and click OK. This will put all
distinct values from col B in both original worksheets into col E of
this 3rd worksheet. Now fill in the other data from cols A, C-G from
the other worksheets in cols D, F-J in this 3rd worksheet. You haven't
provided sufficient details on the contents of these other cols in the
original 2 worksheets to be able to give any advice on how to collect
their data; however, if there are duplicates in col B, then the only
meaningful ways to collect data from the other columns is to calculate
descriptive statistics for them (COUNT [all], COUNT [distinct], SUM,
AVERAGE, MIN, MAX, etc.).