move contract numbers to new column if same ID #.

[bloodybrit90]

Hi Guys,

I have hit a roadblock with my excel skills. In column A1-A70000+ I have Property ID numbers and column B1-B70000+ there are contract numbers. each property ID has up to 3 contract numbers associated with it. I need to move each contract number that matches to C1,D1,E1. if it is the first contract associated with the property id then move to column C in the same row. If it's the second contract associated with that property ID then movie it to column B in the same row.and the same for the third contract.


Is this possible?

 

[bloodybrit90]

I am able to move the first contract number to column C if it's the contract associated with the property ID using this function:

=IF(A3=A2,"",B3)

and I can move the remaining contract numbers that are the same as the one above using this:


=IF(AND(A3=A2,B3=B2),B3,"")

But my it doesnt solve my problem.

Here is an example of what I need:
----A----------------B--------------------C------------D----------E
1--property id---contract_number-----Contract A----Contract B--Contract C
2--8000014------OH10M0268--------OH10M0268
3--8000014------OH10M0268--------OH10M0268
4--8000014------OH16L0082-------------------------OH16L0082
5--8000015------NY06Q9003---------NY06Q9003
6--8000015------MD06L0037-------------------------MD06L0037
7--8000015------MD06L0037-------------------------MD06L0037
8--8000016------MD06M0124-----------------------------------MD06M0124

 

[bloodybrit90]

Bump

Any suggestions would be great.

Thanks,

CG

 

[bloodybrit90]

To the top

Any thoughts?

 

[RonB1111]

Assumes data is sorted ascending with PropID primary and Contract# secondary.
Enter in cells indicated and copy down to end of data:

In cell C2: =IF(A2 <> A1,B2,IF(B2=B1,C1,""))

In cell D2: =IF(C2 <> "","",IF(AND(A2=A1,A2=A3),B2,IF(AND(A2 <> A3,C1 <> ""),B2,IF(B2=B1,B2,""))))

In cell E2: =IF(OR(C2 <> "",D2 <> ""),"",B2)

 

[bloodybrit90]

Thanks RonB1111. It worked.