Help with a formula for data in two spreadsheets

[teriscibetta]

Im sure there is a very basic formula for this but I am a novice and do not know how to do it. I have a spreadsheet with two sheets in it. Sheet 1 has 1 column with a list of alpha-numeric characters in it. Sheet 2 also has 1 column with alpha numeric characters in it. Sheet 2 has some of the same numbers in it as sheet 1 and then some unique ones. I would like to pull out the unique numbers from sheet 2. Does that make sense? Any help would be greatly appreciated

Ex

Sheet 1

Column a
b321
c00576
b4356
b7895
c7799

sheet 2

Column a
b321
c00576
a5678
b4356
b7895
b9999
c7799


I need something to pull out the unique numbers from sheet 2 such as a5678 and b9999

[Pepe Le Mokko]

Have a look at http://www.get-digital-help.com/2009...m-two-columns/

[Tony Valko]

Tell use exactly where the formula is to be entered. Do you want the results listed vertically (down a column) or horizontally (across a row)?

[teriscibetta]

I would love it if the results would be in column 2 on sheet 2 vertically

[Tony Valko]

Try this...

Sheet1

	A
1	Data
2	b321
3	c00576
4	b4356
5	b7895
6	c7799

Sheet2

	A	B
1	Data	Uniques
2	b321	a5678
3	c00576	b9999
4	a5678
5	b4356
6	b7895
7	b9999
8	c7799

This array formula** entered in B2 on Sheet2:

=IFERROR(INDEX(A:A,SMALL(IF(ISNA(MATCH(A$2:A$8,Sheet1!A$2:A$6,0)),ROW(A$2:A$8)),ROWS(B$2:B2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[teriscibetta]

Ok thanks. It appears to work for the first few lines and then stops. I entered the array in B2 on sheet 2 and press enter. The cell then populates. I then click the arrow on the lower right corner of the box for it to run through the rest of the sheet and it only comes back with 8 uniques at the top of column b on sheet 2. What might I be doing wrong?

[Tony Valko]

Not sure. I would have to see the file.

How many uniques do you expect?

[teriscibetta]

The larger sheet has over 2400 items on it. I expect a few hundred results. How could I send you the file?

[Tony Valko]

Make a copy of the file but remove all the data except for what's relevant to this problem then post the file in a reply.

[teriscibetta]

Thank you so much for responding. Is there any way to email or pm it to you? I would rather not put it out there for the world to see

[Tony Valko]

Is there any way to email or pm it to you?

Well, I prefer to keep those aspects of my online identity private.

[newdoverman]

Try modifying the formula given by Tony to include the whole of the ranges being compared. The areas marked in Red are what I am referring to.

=IFERROR(INDEX(A:A,SMALL(IF(ISNA(MATCH(A$2:A$8,Sheet1!A$2:A$6,0)),ROW(A$2:A$8)),ROWS(B$2:B2))),"")

[teriscibetta]

That seems to do the same thing. It only populates the first six lines. I have attached a screen shot

uniques.jpg

[newdoverman]

I see that you have not made any adjustment to the ranges in the formula like I suggested therefore the formula is processing only a small amount of the data.

[teriscibetta]

I am so sorry for my confusion. I really do not know much about excel except for very basic formulas. Can you tell me what the formula should read exactly? Im not sure what to change

[newdoverman]

In B2 try changing the formula to this and enter with Ctrl + Shift + Enter

=IFERROR(INDEX(A:A,SMALL(IF(ISNA(MATCH(A$2:A$2500,Sheet1!A$2:A$2500,0)),ROW(A$2:A$2500)),ROWS(B$2:B2))),"")

[Tony Valko]

I am so sorry for my confusion. I really do not know much about excel except for very basic formulas. Can you tell me what the formula should read exactly? Im not sure what to change

Tell us the EXACT range addresses of your data.