Finding dupicate data in two separate worksheets and combining rows

[step10solutions]

Hello there,

I've searched through the Excel Forum and have seen similar questions, but I am still unclear on what formula or macro I should use. Can someone assist me with the code?

I have two worksheets that I will need to combine the rows from each worksheet if a cell of info matches. Not sure if I am explaining it correctly, but here is an example:

Worksheet 1: Cell Phone Details

Name | Phone Type | Serial Number

Worsheet 2: Cell Phone Billing Details

Serial Number | Plan Type | Monthly Service Charge

if serial # on worksheet 1 matches serial number on worksheet 2, then combine rows from both worksheets and display on new worksheet, to look like:

Name | Phone Type | Serial Number | Plan Type | Monthly Service Charge

Is this possible? I know it is, but I just need help with the formula or macro.

[Richard Buttrey]

Hello there,

I've searched through the Excel Forum and have seen similar questions, but I am still unclear on what formula or macro I should use. Can someone assist me with the code?

I have two worksheets that I will need to combine the rows from each worksheet if a cell of info matches. Not sure if I am explaining it correctly, but here is an example:

Worksheet 1: Cell Phone Details

Name | Phone Type | Serial Number

Worsheet 2: Cell Phone Billing Details

Serial Number | Plan Type | Monthly Service Charge

if serial # on worksheet 1 matches serial number on worksheet 2, then combine rows from both worksheets and display on new worksheet, to look like:

Name | Phone Type | Serial Number | Plan Type | Monthly Service Charge

Is this possible? I know it is, but I just need help with the formula or macro.

Hi,

If this is just a one off exercise, I suggest that you do this manually. If you need it to be done over and over, then the same process could be coded in a simple macro - in which case post back.

Steps.

1. Use a helper column on sheet 1 and enter the following formula in row 2

Please Login or Register  to view this content.

where xxx is the last row of data. Now copy C1 down as far as necessary. Where there is a match with Sheet2 the Serial Number will be returned, where there is no match you'll get a #N/A

2. Now Auto filter sheet 1 with column C as the filter and choose the Not equal to #N/A as the filter. You'll end up with a list of Serial numbers that exist in sheet 2. Copy the filtered records columns A:Ctt to a third sheet in Column C starting in row 2 and put your column headers Name, Phone Type....etc in A1:E1

3. In D2 enter the following formula

Please Login or Register  to view this content.

4. In E1 enter the formula as above changing D$1:D$.... to E$1:E$...

5. Now copy the whole of the data from A1:E... and paste it back using Paste Special as Values, thus converting the VLOOKUP formulae to values.

HTH