Compare and Short Two Columns of Data

[SGamso]

Hi Folks. I'm new to this forum and I'm appreciative of any help anyone can offer. My issue is this I have 2 columns of project numbers, some numbers are in each column and some are unique to each. I need to general two new columns such that each is in sorted order and rows inserted accordingly. For example:

Col A Col B
850 1100
1000 1200
1200 1300
1300 1400
1400 1500
1700 1600
1800 1700
2200 2100
2200
2300
2400


I want the final result to look like this:

Col A Col B
850
1000
1100
1200 1200
1300 1300
1400 1400
1500
1600
1700 1700
1800
2100
2200 2200
2300
2400

This small list can be done manually, but for a list of 1000 or more project numbers this is a problem.
I'm open to any ideas? Macro's, VBA code, whatever.
Thanks. Stan

 

[mazzymx6]

Will there only be two columns of project numbers ?

 

[SGamso]

yes there should be Only Two columns of project numbers

 

[mazzymx6]

Heres a solution

Assuming your data is in columns A+B and that cells A1+B1 are your column headings (ie cell A1 says Col A and cell B1 says Col B)

Call cell C1 "countif col A"
and put the formula below in cell C2
COUNTIF(A2:B10000,A2)


Call cell D1 "countif col B"
and put the formula below in cell D2
COUNTIF(A2:B10000,B2)


Call cell E1 "unique nos in col A"
and put the formula below in cell E2
IF(C2=1,A2,"")


Call cell F1 "unique nos in col B"
and put the formula below in cell F2
IF(D2=1,B2,"")


Call cell G1 "rank"
and put the formula below in cell G2
RANK(E2,$E$2:$F$10000,1)


Call cell H1 "rank continued"
and put the formula below in cell H2
RANK(F2,$E$2:$F$10000,1)


Call cell I1 "list"
and put a 1 in cell I2, a 2 in cell I3 (highlight cells I2+I3 and drag down to row 10,000)


Call cell J1 "number list"
and put the formula below in cell J2
SUMIF($G$2:$H$10000,$I2,$E$2:$F$10000)



Call cell K1 "Col A"
and put the formula below in cell K2
IF(ISNUMBER(VLOOKUP($J2,A:A,1,0)),VLOOKUP($J2,A:A,1,0),VLOOKUP($J2,B:B,1,0))


Call cell L1 "Col B"
and put the formula below in cell L2
IF(ISERROR(IF(VLOOKUP(K2,A:D,3,0)=2,K2,IF(VLOOKUP(K2,B:D,3,0)=2,K2,""))),"",IF(VLOOKUP(K2,A:D,3,0)=2,K2,IF(VLOOKUP(K2,B:D,3,0)=2,K2,"")))




You should now see the results you want in Columns K and L


Hope this help

maz

 

[SGamso]

thank you, I'll will study this and give it a try.

 

[SGamso]

Maz: Thanks. I fiddled with this code and I'm impressed.
Can you explain how SumIf works across multiple columns of data? I've looked over the web and I don't see an example of your code out there. But then again, there appears to be a lot of code that has not been documented.
Thanks again! Stan

 

[Peter_SSs]

SGamso

Your best solution may be some VBA code but that is not my strong point so here is another formula solution that won't take as many columns.

1. In C1 put a heading (anything, but the cell should not be blank).
2. Don't really need it but might as well put a heading in D1 as well.
3. Formula in C2: =IF(OR(C1=MAX($A$2:$B$100),C1=""),"",SMALL($A$2:$B$100,1+COUNTIF($A$2:$B$100,"<="&C1)))
4. D2: =IF(COUNTIF($A$2:$B$100,C2)>1,C2,"")
5. Select C2:D2 and copy/drag down as far as needed.

Mr Excel.xls
	A	B	C	D	E
1	A	B	List 1	List 2
2	850	1100	850
3	1000	1200	1000
4	1200	1300	1100
5	1300	1400	1200	1200
6	1400	1500	1300	1300
7	1700	1600	1400	1400
8	1800	1700	1500
9	2200	2100	1600
10	2200		1700	1700
11	2300		1800
12	2400		2100
13			2200	2200
14			2300
15			2400
16
Sheet1