0

I have an excel file with 5 rows in column A and B, and 3 in column C and D (in reality though, I have a couple of hundreds of rows). Column B consists of text belonging to A, and D of text belonging to C. Column C has some of the values found in column A.

It looks like this:

        A       B        C         D


1       1    stringA1    1      stringC1
2       2    stringA2    2      stringC2
3       3    stringA3    4      stringC3
4       4    stringA4    
5       5    stringA5

Now, I would like to match the numbers in column C with those in A, so that matches are put in the same row. For those rows in A for which there is no match in C, I want to have blank cells after column B.

It would look like this in this case:

        A       B        C         D


1       1    stringA1    1      stringC1
2       2    stringA2    2      stringC2
3       3    stringA3    
4       4    stringA4    4      stringC3
5       5    stringA5

I have some idea that I should use VLOOKUP and maybe Conditional Formatting, but unfortunately I am not very experienced in excel. Could someone please suggest a way to do this?

Sofie Molin Andersson
  • 127
  • 1
  • 4
  • 8

2 Answers2

1

Enter the following formula in Cell E1:

=IF(IFERROR(MATCH(A1,$C$1:$C$5,),"")<>"",INDEX($C$1:$D$5,IFERROR(MATCH(A1,$C$1:$C$5,),""),1),"")

and this one in Cell F1:

=IF(IFERROR(MATCH(A1,$C$1:$C$5,),"")<>"",INDEX($C$1:$D$5,IFERROR(MATCH(A1,$C$1:$C$5,),""),2),"")

enter image description here


Using Helper Column:
You can also do this using a helper column.

In Cell E1 write:

=IFERROR(MATCH(A1,$C$1:$C$5,),"")

Then in Cell F1 write:

=IF(E1<>"",INDEX($C$1:$D$5,E1,1),"")

And finally in Cell G1 write:

=IF(F1<>"",INDEX($C$1:$D$5,E1,2),"")

This was answered by @user3514930 to a question here.

Community
  • 1
  • 1
Mrig
  • 11,612
  • 2
  • 13
  • 27
  • Thanks! This partly solves my problem, as it correctly moves the parts in column C and D down. But when I try it, it doesn't remove the data and strings after it has moved them down. In other words, I don't get a blank row. – Sofie Molin Andersson May 26 '16 at 11:10
  • As I've shown in the image, formula need to be entered in new columns and then you'll have to delete columns `C` and `D`. – Mrig May 26 '16 at 11:14
  • Yes, of course (I meant columns E and F in my comment, sorry..). I meant that I don't get the blank parts in column E and F, on row 3 and 5 in this example. Instead I get a copy of the cell above it. Does one have to delete that by hand? In my real problem (which has hundreds of rows), that wouldn't be viable. I'm sorry for this confusion. – Sofie Molin Andersson May 26 '16 at 11:28
  • @SofieMolinAndersson - No, you should get blank rows 3 and 5 based on above example. – Mrig May 26 '16 at 12:07
  • Now it works perfectly, thank you!! It was the 0 in MATCH(..,..,0) that was missing. This made my day, thanks :D – Sofie Molin Andersson May 26 '16 at 12:28
  • @SofieMolinAndersson - Great! – Mrig May 26 '16 at 12:30
0

You can directly use this formula in D2 and copy downwards:

=IF(A2 = C2, A2, "")

Now if you have formulas in A2, C2, type in those formulas in place of A2, C2 in the above.

Chaos Legion
  • 2,730
  • 1
  • 15
  • 14
  • Thanks! Although, how can I use it "directly" in C2 where I already have a number? I'm not sure what you mean. If I put it in C2 it becomes a circular reference, as I am refering to the cell I'm standing in in the logical comparison in the IF-formula. – Sofie Molin Andersson May 26 '16 at 11:06
  • Sorry I meant D2. Corrected – Chaos Legion May 26 '16 at 11:50