Identifying & Merging Duplicate Rows within a Range

[wadsy]

Hey there I am having an issue where I cant find a formula to do the following:

I have four columns two of which contain identical text (which is sorted in a different order). Next to each text cell is a corresponding number in the next column. So A1 contains text and B2 contains a corresponding number. Column C2 contains text and D2 contains a different corresponding number.

What I want to do is to find the matching text from the two text columns and merge (the identical text)and move the corresponding number from the duplicate cell to the same row so I end up with:

A1 (text) B2 (number) D2 (number) or text number number

Another way to explain it is:

I have two lists of matching text but with different numbers in the cells next to each column of text. I want to move the matching text and the number next to it.

Would be very grateful for some help cheers

[Simon Lloyd]

To be clear, is the matching text always on the same row? if it is you want to get rid of the text in column C and place the number from column D in its place....is this correct? if this is correct the below will do what you need:

Please Login or Register  to view this content.

[wadsy]

Thats great thanks very helpful. I was hoping to find a formula that works even if the they are not in the same row as well though for the times when the text is not always the same and some text is omitted.

What I am doing is I have a list of keywords with a corresponding number of people searching for that keyword eg

Column A says "natural remedies for scalp" then column B says 2375

Then I have the same list of keywords in a different order (some duplicated) with a different figure:

Column C says "natural remedies for scalp" then column D says 5

I was hoping to find a formula that would first find the matching row and then move the figure from Column D to either column E or B

I can paste the second set of columns (C & D) to another worksheet page

[Simon Lloyd]

You need to provide a sample workbook so we can see the layout and work with someof your dummy data. A formula cannot do what you want as its only function is to return a result it cannot delete, move or physically manipulate data.

[wadsy]

I have attached the workbook - really grateful for your help thanks. Yes I can see a macro needs to do this. Keen to buy your course(s) to learn how to use them too

What I would like to end up with as a result from the data in the workbook is:
Keyword Search Volume Ranking
"storage" 49500 1

Have attached excel 2003 and 2007 files

Column A & B remain static>>Search for and find the matching row text that matches the text in column A>>move and add the adjacent number so it is the same row as the original text in column A