Excel Sorting help

[Guest]

Here's a suggested way working along similar lines as earlier in this thread ..

See sample file at:
http://cjoint.com/?jiq1Y25hON
Nate_1.xls

In Input Asset Ownership:

List the 3 owners across in K3:M3,
consistent with the values in "Owner" col, ie: 1, 2, 1&2

In K4:
=IF($C4="","",IF($C4=K$3,ROW(),""))
K4 copied across to M4, filled down to M23

In Asset Ownership:

In A6:
=IF(B6="","",ROW(A1))

In B6:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$K$4:$K$23),"",INDEX('Input Asset
Ownership'!A$4:A$23,MATCH(SMALL('Input Asset
Ownership'!$K$4:$K$23,ROW(A1)),'Input Asset Ownership'!$K$4:$K$23,0)))

In C6:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$K$4:$K$23),"",INDEX('Input Asset
Ownership'!D$4$23,MATCH(SMALL('Input Asset
Ownership'!$K$4:$K$23,ROW(A1)),'Input Asset Ownership'!$K$4:$K$23,0)))
Copy C6 to D6

In E6:
=IF(OR(C6="",D6=""),"",C6-D6)

Select A6:E6, copy down to E18

(This settles it for assets owned by 1)

Then in a similar vein for assets owned by 2 ..

In A21:
=IF(B21="","",ROW(A1)+MAX($A$6:$A$18))
The above will auto-continue the numbering from the assets owned by 1

In B21:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$L$4:$L$23),"",INDEX('Input Asset
Ownership'!A$4:A$23,MATCH(SMALL('Input Asset
Ownership'!$L$4:$L$23,ROW(A1)),'Input Asset Ownership'!$L$4:$L$23,0)))

In C21:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$L$4:$L$23),"",INDEX('Input Asset
Ownership'!D$4$23,MATCH(SMALL('Input Asset
Ownership'!$L$4:$L$23,ROW(A1)),'Input Asset Ownership'!$L$4:$L$23,0)))
Copy C21 to D21

In E21:
=IF(OR(C21="",D21=""),"",C21-D21)
Select A21:E21, copy down to E26

Finally, for assets owned by 1&2 ..

In A29:
=IF(B29="","",ROW(A1)+MAX($A$21:$A$26))
The above will auto-continue the numbering from the assets owned by 2

In B29:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$M$4:$M$23),"",INDEX('Input Asset
Ownership'!A$4:A$23,MATCH(SMALL('Input Asset
Ownership'!$M$4:$M$23,ROW(A1)),'Input Asset Ownership'!$M$4:$M$23,0)))

In C29:
=IF(ROW(A1)>COUNT('Input Asset Ownership'!$M$4:$M$23),"",INDEX('Input Asset
Ownership'!D$4$23,MATCH(SMALL('Input Asset
Ownership'!$M$4:$M$23,ROW(A1)),'Input Asset Ownership'!$M$4:$M$23,0)))
Copy C29 to D29

In E29:
=IF(OR(C21="",D21=""),"",C21-D21)
Select A29:E29, copy down to E35


---

Nate's clarification:

.... As you can see what I specifically need is to get one input list of
info into multiple output lists on the same page. There will always be
three potential owners (1,2, or 1&2) although the number of assets
owned by each will vary.

The extra info on the input sheet goes to other places and should not
be heeded attention.

Lastly, if anyone is feeling particularly genuis, I could use a way to
automatically number the assets on the "asset ownership" sheet. In
other words, IF owner one has three assets listed they are numbered as
such. More importantly, however, I need owner 2's first asset in the
list to pick up at numeral four and continue from there.

 

[kastnna]

Pure genius.
Thanks everyone for the help especially Max.

 

[Guest]

You're welcome !