Line up cells based on content

  • Thread starter Thread starter Paul Banco
  • Start date Start date
P

Paul Banco

Need to know if there is a way to line up cells based on data. e.g in column
A I have numbers 1200 through 1300 and the numbers can repeat themselves
many of times, this is why sorting will not work. In column B I have the
same numbers but in my case I could be missing some number(s), so in column
A I may have 1250 listed 20 times and column B may have it listed only 10
times. I need to know if I can line them up to see of I am missing any
numbers. See below


This is what I have now
1256 1252
1256 1252
1257 1252
1257 1252
1257 1252
1257 1252
1257 1252
1257 1252
1257 1256
1258 1256
1258 1257
1258 1257
1258 1257
1258 1257
1258 1257
1258 1257
1258 1257
1258 1258
1258 1258


This is what I would like
A B
1200 1200
1200 1200
1201
1201
1202 1202
1202 1202




Any one have any ideas?

Thanks
Paul
 
Hi

I would suggest having a look at using the COUNTIF() function. Rather than
sort, and resort and massage the data into a useable list, it might be
easier to just get total counts of the entries.
 
If column B is always a subset of column A (no numbers in B that are not
in A, and no more occurrences of a number in B than in A) then you
could do the following:

Put a sorted version of column A somewhere (column D in this formula)
and in the adjacent column (E in the example) you could use
=IF(SMALL($B$1:$B$1000,1)=D1,SMALL($B$1:$B$1000,1),"")
in E1, and
=IF(SMALL($B$1:$B$1000,COUNT(E$1:E1)+1)=D2,SMALL($B$1:$B$1000,COUNT(E$1:E1)+1),"")
in E2. Copy E2 down, and you will have the desired display.

You could also use the SMALL() function to produce your sorted version
of column A if you want.

Jerry
 
Back
Top