Home |
Search |
Today's Posts |
#1
|
|||
|
|||
HELP HELP HELP HELP HELP
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. |
#2
|
|||
|
|||
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.). |
#3
|
|||
|
|||
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? "Harlan Grove" wrote: 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.). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|