Match Rows

[TheAnalyst]

Hello,
I have several projects that involve this:
The easiest:
I have column A with keywords (10 to 100). Column B has values associated with those words(number of times that search was made in the website). I have to compare from week to week, I can sort the words, but sometimes some of the keywords are missing. It takes too long and too much clicking. And it's prone to errors

Week 1
A=100 keywords B=100 values
Week 2
D=80 keywords E=80 Values
Is there a way that I can just line up the values of week 2 with their corresponding keyword on week 1 so that the worksheet looks like so;
A= n keywords B= Week1 Values C=Week2 Values, etc

Thank you for your help

[starguy]

Hello,
I have several projects that involve this:
The easiest:
I have column A with keywords (10 to 100). Column B has values associated with those words(number of times that search was made in the website). I have to compare from week to week, I can sort the words, but sometimes some of the keywords are missing. It takes too long and too much clicking. And it's prone to errors

Week 1
A=100 keywords B=100 values
Week 2
A=80 keywords B=80 Values
Is there a way that I can just line up the values of week 2 with their corresponding keyword on week 1 so that the worksheet looks like so;
A= n keywords B= Week1 Values C=Week2 Values, etc

Thank you for your help

it would be easy to understand and make a solution if you attach here a sample file.

[Bryan Hessey]

Hello,
I have several projects that involve this:
The easiest:
I have column A with keywords (10 to 100). Column B has values associated with those words(number of times that search was made in the website). I have to compare from week to week, I can sort the words, but sometimes some of the keywords are missing. It takes too long and too much clicking. And it's prone to errors

Week 1
A=100 keywords B=100 values
Week 2
A=80 keywords B=80 Values
Is there a way that I can just line up the values of week 2 with their corresponding keyword on week 1 so that the worksheet looks like so;
A= n keywords B= Week1 Values C=Week2 Values, etc

Thank you for your help

Hi,

As Starguy said, it really is easier if you supply a small sample of your data, however, possibly what you are seeking is a lookup for column C, as in C1 put

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(A1,Sheet2!A:B,2,FALSE))

and formula fill that down column C for the extent of your column A data. This should bring all values from Sheet 2 for the items in this sheet.

Note however,
It assumes each column A item appears only once in this sheet and in Sheet2
It will leave blank spaces for items not found on Sheet2
It will NOT detect items on sheet2 that do not appear on Sheet1

I would suggest a =CountA(C:C) be compared to =CountA(B:B) on sheet2.

Let us know how you go.
---

Formula Fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm

[Bryan Hessey]

thread:
Title: Match Rows
12-08-2006, 03:39 PM

Hi,
I have loaded the file to see if you can help me.

Thank you

Hi,

When you do this you should add it as a new post, not amend a post that has been read.

Your data contains spurious characters, the word "*** " is noted as 4 characters long, and it is not a space.

You might want to clean your data.

The VLookup provided will do the job, get the list as in C, Copy column C, then Paste Special = Values back over itsself. (as shown in column N)
You can then delete colums D and E

Let us know how you go.
---

[TheAnalyst]

Bryan Hessey,
I works like a charm, you are the vlook up master.
Thank you sir

Die dulci fruimini.

[Bryan Hessey]

Bryan Hessey,
I works like a charm, you are the vlook up master.
Thank you sir

Die dulci fruimini.

Hi,

You still need to cleanup that column A data, try in column (say) H. in H1 put

=TRIM(SUBSTITUTE(A1,"*",""))

and formula-filll that to the extent of your column A data, then
Copy column H, and select A1 and Paste Special = Values
then remove column H.

note, you may need to copy the Trim(Substitute from the column C formula, the web may lose the odd character.

hth
---