Re Post: Re-arranging Cells to a specific

[gregsiuc]

I'm not sure what I did wrong on the first post mods please delete the first thread

Hi
I have an issue at work i'm trying to resolve. We have a list of of serial and asset numbers that were mixed do to a sorting problem. Numbers in the list starting with 2Z=Serial numbers, numbers starting with CD=asset numbers the list in column A should be arranged in the following pattern (serial,asset,serial,asset....) when there is two assets together the pattern reverses and picks up going (asset,serial,asset,serial) until it hits two serial numbers in a row and then reverts back to the correct pattern of (serial, asset,serial,asset....). Column B is how column A should be arranged. Is there a function or subroutine that can help with this? I tried using an "if" function similar to the one below but it just does not seem to work out. I have about 4000 serial asset combinations that i have to reconcile by hand and would love to find a way to automate it. Suggestions or help on what i could try would be very much appreciated.

=IF(AND(LEFT(E2,2)="2U",LEFT(E3,2)="CD",E2<>F1),E2,IF(AND(LEFT(E2,2)="CD",LEFT(E3,2)="CD"),E2,IF(AND(LEFT(E2,2)="CD",LEFT(E3,2)="2U",LEFT(E1,2)="2U",LEFT(#REF!,2)="2U"),E2,IF((AND(LEFT(E2,2)="CD",LEFT(E3,2)="2U")),E3,IF(F1=E2,E1)))))

Column A (2ZA7020775,CD9921379,2ZA702076C,CD9921375,2ZA702076X,
CD9921402,CD9921374,2ZA70207F5,CD9921373,2ZA7010SY0,CD9921382
,2ZA7010SXQ,2ZA7010SYT,CD9921397,2ZA7030771,CD9922078)

Column B
(2ZA7020775,CD9921379,2ZA702076C,CD9921375,2ZA702076X,CD9921402,
2ZA70207F5,CD9921373,2ZA7010SY0,CD9921373,2ZA7010SXQ,CD9921382,
2ZA7010SYT,CD9921397,2ZA7030771,CD9922078)

Thanks
Greg

 

[gregsiuc]

Problem solved

Figured it out.

=IF(AND(LEFT(E6,2)="2Z",F6=1),E6,IF(AND(LEFT(E6,2)="CD",F6=2),E6,IF(AND(LEFT(E6,2)="CD",F6=1),E7,IF(AND(LEFT(E6,2)="2Z",F6=2),E5))))

thanks

greg