Sorting Values of Two Columns

[forestavekids]

Hi guys,

Have 2 columns in the attached.

Pretty simple, I am trying to find what values are in BOTH column A and B.

Please see attached.

Thanks!

[JieJenn]

****deleted*****

[oeldere]

d2 =iferror(match($B3,$A$2:$A$233,0)>=1,"") and filter on true.

OK this is not the whole solution since it only looks in column B.

I will post a revisted one later on.

[Glenn Kennedy]

You could use conditional formatting:

=NOT(ISNA(VLOOKUP(A2,$B$2:$B$233,1,FALSE))) for column A and

=NOT(ISNA(VLOOKUP(b2,$a$2:$a$116,1,FALSE)))

[forestavekids]

You could use conditional formatting:

=NOT(ISNA(VLOOKUP(A2,$B$2:$B$233,1,FALSE))) for column A and

=NOT(ISNA(VLOOKUP(b2,$a$2:$a$116,1,FALSE)))

I was looking to have the values that are NOT in both column A and B to paste over to column D.

[Glenn Kennedy]

That's not what you asked for at Post 1!!!

[forestavekids]

You could use conditional formatting:

=NOT(ISNA(VLOOKUP(A2,$B$2:$B$233,1,FALSE))) for column A and

=NOT(ISNA(VLOOKUP(b2,$a$2:$a$116,1,FALSE)))

I was looking to have the values that are NOT in both column A and B to paste over to column D.


My mistake, though that was a given.

[FlameRetired]

I was looking to have the values that are NOT in both column A and B to paste over to column D.


My mistake, though that was a given.

That would be near infinite.

Possibly numbers in A not in B or the other way around?

[FlameRetired]

Not sure what you want.

Here are 3 possibilities. All are array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Then fill down until you get blanks.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In A not B	In B not A	Both A & B
=IFERROR(INDEX($A$2:$A$116,SMALL(IF(ISNA(MATCH($A$2:$A$116,$B$2:$B$233,0)),ROW($A$2:$A$116)-MIN(ROW($A$2:$A$116))+1),ROWS($2:2))),"")	=IFERROR(INDEX($B$2:$B$233,SMALL(IF(ISNA(MATCH($B$2:$B$233,$A$2:$A$116,0)),ROW($B$2:$B$233)-MIN(ROW($B$2:$B$233))+1),ROWS($2:2))),"")	=IFERROR(INDEX($B$2:$B$233,SMALL(IF(ISNA(MATCH($B$2:$B$233,$A$2:$A$116,0)),"",ROW($B$2:$B$233)-MIN(ROW($B$2:$B$233))+1),ROWS($2:2))),"")

[oeldere]

Had no time left yesterday.

another approach.

copy column A to column E

copy column B to column E (below the earlier copied data)

After that => data => remove duplicate.

See the attached file.

[Glenn Kennedy]

This array formula merges the two lists, removing duplicates (so... if a value occurs more than once in the source data, it appears only once in the results: if value x appears on both lists it will appear once in the results

Flame's formulae differ in that if a value appears in column B, it is EXCLUDED from the column A list (and vice versa). So if a value occurs on both lists it will not appear ANYWHERE in the results.

Please Login or Register  to view this content.

[Glenn Kennedy]

oh yes... and to stop the results going into scientific notation, apply custom formatting to the cells: 0

[FlameRetired]

I'm still not certain of my interpretations.

I used a simplified small number example. (I am not able to make the method I used here work with such large numbers.)

In column C the range of numbers from MIN to MAX of A and B.
In column D a complete list of all A and B.
In column E a list of numbers in the range, but not found in A or B.

Does this represent what you mean?

Row\Col	A	B	C	D	E
1	A	B	Range of Numbers	All Numbers	Missing Numbers
2	37	35	30	30	31
3	45	32	31	30	34
4	35	45	32	30	36
5	43	45	33	32	39
6	33	42	34	32	44
7	38	32	35	32	46
8	32	40	36	33
9	30	42	37	35
10	47	41	38	35
11	47	30	39	37
12	37	30	40	37
13			41	38
14			42	40
15			43	41
16			44	42
17			45	42
18			46	43
19			47	45
20				45
21				45
22				47
23				47

[forestavekids]

Flame, had to look up this thread again and ran into a problem with the formulas. I was trying to apply all 3 (see attached). For some reason the total values in A and B is not equaling the total amount of cells in columns D, E, F. They should match up identical. Any idea?

[oeldere]

Why did you not replied on the offered solution in oktober 2016?

Flame, had to look up this thread again

You get better help if you not excluding forummembers for helping you.

[Glenn Kennedy]

Take a look here. The numbers all match up.

Your logic is incorrect, however. the total sorted number is A only plus B only plus two times the number of unique values in both columns

[FlameRetired]

Withdrawn by FR.