Spread rows to match data in other column?

[SethCallen]

I have two lists that I would like to be able to align the rows, where one list has more than the other.
For example:

Column A
Row 1 Apple
Row 2 Banana
Row 3 Lemon
Row 4 Orange



Column B
Row 1 Apple
Row 2 Banana
Row 3 Broccoli
Row 4 Celery
Row 5 Carrot
Row 6 Lemon
Row 7 Orange

Is there a way to spread the matching values out into the matching rows? If I just paste the two columns, they will not line up correctly - is there some sort of auto-sort function that will move the rows?
So that the end result would look like this:

Apple Apple
Banana Banana
Broccoli
Celery
Carrot
Lemon Lemon
Orange Orange

(which column is which is not as important as spreading them out into rows.)
Edit: attaching "before" and "after" file examples here:
after.xlsxbefore.xlsx
I need to be able to take a list of numbers and either highlight or somehow match the ones that show up in both columns.

[mcmahobt]

If you don't want to do this using VBA, I suggest the method:

In your "Before" workbook, insert a column between columns A and B (so that your shorter list is now in column C).

Paste this formula into cell B1 and drag down:

Please Login or Register  to view this content.

If you want to keep this result, copy the column B range and paste values to confirm, then delete contents in column C.

Hope this helps!

[SethCallen]

Thanks for that... it seems to work in the simple example with text fields, however, my goal is to sort two columns of numbers (I used the fruit/vegetables for a simple example) and when I apply that same formula =IFERROR(INDEX($C$1:$C$5,MATCH(A1,$C$1:$C$5,0)),"")
in a worksheet that contains numbers, it does not seem to work at all. Is there something different to do if using numbers?

[mcmahobt]

It should work with numbers as well. It is hard to give direct help without a sample workbook to work from, however.

[newdoverman]

This shows the fruit with random numbers assigned to each fruit. The values that are in the other range are repeated and if not a blank is returned.

	C	D	E	F	G	H	I	J
1	Apple	11	11			Apple	11	11
2	Banana	12	12			Banana	12	12
3	Orange	6				Broccoli	31	31
4	Carrot	7				Kumquat	43	43
5	Celery	8				Melon	54	54
6	Broccoli	31	31
7	Kumquat	43	43
8	Tangerine	9
9	Lemon	10
10	Melon	54	54
11
12		=IFERROR(VLOOKUP(D1,$I$1:$I$5,1,0),"")
13
14		=IFERROR(VLOOKUP(I1,$D$1:$D$10,1,0),"")

[SethCallen]

Thanks Newdoverman, although I don't see the need to add random numbers etc. I merely want to align the rows.
I am attaching (inserting?) the actual working Excel sheet - it's two columns of UPC codes that I want to match up, so that we can see what items we're missing ordering from a vendor, this way we're not working with the fruit example but the actual numbers.
This is the file WorkingExample.xlsx
Working Example.xlsx
(As a side note, if there is a way to add spacing to rows that don't exist in both columns, that could be helpful too. In other words, not both vendors might carry a product so a way to show that would be useful I suppose.
Thanks much!!

[newdoverman]

Enter this in column B and fill down:

Please Login or Register  to view this content.

This is how it turns out:

	A	B	C
1	000004001003		000056454253
2	000056454024		000056454406
3	000093450294		000056454604
4	000093450300		000056454611
5	000093450393		000056454628
6	000360000016	000360000016	000056454635
7	000360000030	000360000030	000056454666
8	000360000061	000360000061	000056454673
9	000360055009		000056454680
10	000370000051	000370000051	000056454703
11	000433821487		000056454727
12	000433821494		000360000016
13	000433821500		000360000030
14	000433821517		000360000061
15	000433905323		000360000078
16	000433905422		000360000085
17	000433905521		000360000092
18	000680000017	000680000017	000360053005
19	000680000024	000680000024	000360054002
20	000680000031	000680000031	000370000037
21	000680000079	000680000079	000370000051
22	000790200116	000790200116	000370000075
23	000790200123	000790200123	000518100100
24	000790200130	000790200130	000518100186

[newdoverman]

Here is your workbook with the above formula but added a check that tests the values in C against those in A

[SethCallen]

Awesome thanks guys!!! Getting closer I just need to play around with that a bit now. Newdover - by "tests the values in C against those in A" what do you mean - is that showing what items both columns contain?

[newdoverman]

By having both versions, you can look at either column to see what matches....just a convenience.

[SethCallen]

Thanks very much!!