Apologies if this doesn't format correctly, not sure what the < PRE > tag does :\ I've read this - http://www.computing.net/howtos/sho... - but I don't know why things don't line up. I want to compare the keyword columns (below) so that all the same keywords are in the same row and the rest are sorted alphabetically.

So turn this

Keyword Traffic Keyword Traffic Keyword Traffic 1 arcade 1000 games 800 pacman 123 2 tmnt 200 arcade 500 daytona usa 78 3 simpsons 150 narc 15 donkey kong 54 4 narc 20 mortal kombat 42 narc 12 5 mortal kombat 65 pacman 789 arcade 200into this

Keyword Traffic Keyword Traffic Keyword Traffic 1 arcade 1000 arcade 500 arcade 200 2 daytona usa 78 3 donkey kong 54 4 games 800 5 mortal kombat 65 mortal kombat 42 6 narc 20 narc 15 narc 12 7 pacman 789 pacman 123 8 simpsons 150 9 tmnt 200Image of tables - http://i.imgur.com/VXy8W.jpg

I found this post - http://www.computing.net/answers/of... - but I don't understand vba enough to try and figure it out :(

My columns are about 500 keywords long should that make a difference, but I'd be thankful for anybody that can help me in the right direction.

You can do this with a series of VLOOKUP functions. First, your table layout doesn't make sense. You don't have a Row number for your Column headings, so I will have to assume that your table actually starts in A1 and the first occurrence of "arcade" is in A2. That is what I will assume with the formulas below.

The first thing you need is a list of unique items from your list, sorted alphabetically. For the purpose of this exercise, I will place my list in I1

I 1 Keyword 2 arcade 3 daytona USA 4 donkey kong 5 games 6 mortal kombat 7 narc 8 pacman 9 simpsons 10 tmntIn J2, place this formula:

=IF(ISNA(VLOOKUP($I2,$A$2:$B$6,1,0)),"",VLOOKUP($I2,$A$2:$B$6,1,0))

In K2, place this formula:

=IF(ISNA(VLOOKUP(I2,$A$2:$B$6,2,0)),"",VLOOKUP(I2,$A$2:$B$6,2,0))

Drag this down to the bottom of the list in Column I and you should get:

J K 1 2 arcade 1000 3 4 5 6 mortal kombat 65 7 narc 20 8 9 simpsons 150 10 tmnt 200In Columns L & M change the arguments of the VLOOKUPS to match the ranges in the next 2 columns of your original table and then do the same thing for Columns N & O.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for the reply (and I think I see what I did wrong with the tables). I worked my way through the problem and fixed it while replying.

In case anyone else has this problem my fixes were to change the table ranges in the J column (instead of $A$2:$B$6, have $A$2:$B$501, so have it as long as the data you're comparing) and then in the K column change the table range as before but then look in column 1 for the first part.

So instead of

=IF(ISNA(VLOOKUP(I2,$A$2:$B$6,2,0)),"",VLOOKUP(I2,$A$2:$B$6,2,0))have

=IF(ISNA(VLOOKUP(I2,$A$2:$B$501,1,0)),"",VLOOKUP(I2,$A$2:$B$501,2,0))Anyway, all working for me now thank you again.

I'm not what you mean by your last statement: "...change the table range but then look in column 1 for the first part. VLOOKUP

alwayslooks in the first column of thelookup_table. You can't change that. The 3rd argument,col_index_number,(the 1 or 2 in this case) determines which column of the table VLOOKUPreturnsthe value from. In other words, VLOOKUP is always looking for the Keyword in thelookup_tableand then either returning the Keyword or the Traffic value depending on whether thecol_index_numberis 1 or 2.Read up on the VLOOKUP function in the Excel help files or via a Google search to get a full explanation of the VLOOKUP arguments.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

To hopefully clarify, the column index number is what needed to be changed. In your example the first argument for the K column was looking in column index 2 (traffic) instead of 1 (keyword).

So just wanted to make sure others check that the lookup table includes all of their rows and then make sure they're comparing the keyword column in the first argument and the traffic column in the second.

Make sense?

I also added a zero to the if_true (between the ""), as it gave me problems having no number in there when trying to filter the data later :)

re: To hopefully clarify, the column index number is what needed to be changed.

In your example the first argument for the K column was looking in column index 2 (traffic) instead of 1 (keyword).bzzzzzt! Wrong answer!

Did you read the Help files on the VLOOKUP function as I suggested?

Here is the syntax of the VLOOKUP function directly from the Excel Help files:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here is the description of the

table_arrayargument, my emphasis added:

table_arrayRequired. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in thefirst column of table_arrayare the values searched by lookup_value.In other words the VLOOKUP function

alwayslooks in thefirst columnof thetable_arraywhich in your case is $A$2:$B$501. You can not change that.Now here is the the description of the

col_index_numargument:

col_index_numRequired. The column number in the table_array argument from which the matching value must bereturned.Putting those 2 descriptions together, here is how the formula works:

The VLOOKUP will

alwayslook in the first column of thetable_array(e.g. Column A if the table_array is $A2:B501, Column F if the table_array is $F$1:$Z$50) and return the value from the column number specified by the col_index_num argument.Since your table_array is only 2 columns wide, your col_index_num can only be 1 or 2. In your case, if it's 1, it will return the value from Column A. If it's 2 it will return the value from Column B. If your table_array was $F$1:$Z$50, a col_index_num of 1 would refer to Column F, a col_index_num of 5 would refer to Column J.

The col_index_number has

nothingto do with where the VLOOKUP function searches for the lookup_value.Changing this:

=IF(ISNA(VLOOKUP(I2,$A$2:$B$501,1,0)),"",VLOOKUP(I2,$A$2:$B$501,2,0))to this:

=IF(ISNA(VLOOKUP(I2,$A$2:$B$501,2,0)),"",VLOOKUP(I2,$A$2:$B$501,2,0))will make absoutey no difference because the ISNA is only concerned with whether or not the lookup_value is found in the first column of the lookup_table. The ISNA function doesn't care about or even know about the col_index_number.

If you want to proof that to yourself, use any col_index_num value you want for the first part of the formula (the VLOOKUP within the ISNA function), e.g. 45, and the formula will still work, even if there is no Column 45 in your table_array. Use 45 in the second part and the formula will fail.

Now that you have everything working, try this formula and you'll see what I mean.

=IF(ISNA(VLOOKUP(I2,$A$2:$B$501,45,0)),"",VLOOKP(I2,$A$2:$B$501,2,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Played around with it again and it seems as if changing the number does indeed have no effect, sorry. It definitely didn't work as I pasted it but I must have been confused as to what I changed when I fixed it.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History