Solved Align columns by rows with identical values

October 11, 2012 at 12:28:42
Specs: Windows Vista
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	200

into 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	200	

Image 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.


See More: Align columns by rows with identical values

Report •

#1
October 11, 2012 at 14:07:24
✔ Best Answer
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  tmnt

In 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	        200

In 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.


Report •

#2
October 12, 2012 at 02:32:33
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.


Report •

#3
October 12, 2012 at 05:13:18
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 always looks in the first column of the lookup_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 VLOOKUP returns the value from. In other words, VLOOKUP is always looking for the Keyword in the lookup_table and then either returning the Keyword or the Traffic value depending on whether the col_index_number is 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.


Report •

Related Solutions

#4
October 12, 2012 at 05:32:44
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 :)


Report •

#5
October 12, 2012 at 09:30:45
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_array argument, my emphasis added:

table_array Required. 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 the first column of table_array are the values searched by lookup_value.

In other words the VLOOKUP function always looks in the first column of the table_array which in your case is $A$2:$B$501. You can not change that.

Now here is the the description of the col_index_num argument:

col_index_num Required. The column number in the table_array argument from which the matching value must be returned.

Putting those 2 descriptions together, here is how the formula works:

The VLOOKUP will always look in the first column of the table_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 nothing to 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.


Report •

#6
October 12, 2012 at 09:56:52
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.


Report •

Ask Question