Getting circular reff. Need to get formulas to ignore duplicate and go to next cell

[geniukas]

Hello,
So I need for formulas to somehow ignore if next cell is duplicate and go down till it gets new value.

From table ABCDEG if any of them got value next to them I need that to show in order with no duplicates. Not if values are next to ACEG all i get is ACCEEG my target is to get ACEG-- and this should work with any combination and end up in alphabetical order. When i try to get them to go to other value if its same as itself I get circular references eventually. Can someone help with smarter formulas or other ways. Thank you.

Formulas:
IF(AC4>0,AA4,IF(AC5>0,AA5,IF(AC6>0,AA6,IF(AC7>0,AA7,IF(AC8>0,AA8,IF(AC9>0,AA9,""))))))
IF(AC5>0,AA5,IF(AC6>0,AA6,IF(AC7>0,AA7,IF(AC8>0,AA8,IF(AC9>0,AA9,"")))))
IF(AC6>0,AA6,IF(AC7>0,AA7,IF(AC8>0,AA8,IF(AC9>0,AA9,""))))
IF(AC7>0,AA7,IF(AC8>0,AA8,IF(AC9>0,AA9,"")))
IF(AC8>0,AA8,IF(AC9>0,AA9,""))
IF(AC9>0,AA9,"")

[Glenn Kennedy]

Hi. The example you gave may not be very good. In your example ALL of the entries in the first column are already in alphabetical order. Is that always the case? Secondly, there are no duplicates in the first column, yet you talk about duplicates. So. If your example is NOT representative, please attach an Excel sheet WITH representative data. This formula works with your example:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$7)/($B$2:$B$7="X"),ROWS(D$2:D2))),"")

If you do need to attach a file... Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[geniukas]

Sorry for confusing explanation. It sounded better in my head just was hard to explain.
Found way out anyway but if someone got my drift and will need help will attach what sorted me out.
Its not 100% what i needed but I made it work with few adjustments.

Anyway can close this down Im good

[Glenn Kennedy]

Glad you got sorted.... but i still don't understand how your file at post 3 relates to the picture at post 1!!

[geniukas]

Imagine if next to ABCDEG you add amount of workers at each position. if position isnt needed you leave blank and move on but it still showes up in other list that i wanted to rank just ones that are with assigned labor. This is to be printed out for staff to see who what and where they will be going that day. Its just a small cell of a big staff sheet. I didnt wanted to print positions that wont be working that day. Each position would have 8 people names next to it. So I didnt needed ranking for values or stuff like that I needed to get my working clusters for that day to be on top in abc order. Hope that clears it out. If you want to see nearly done sheet can share it no problem.

[Glenn Kennedy]

Here's a helper-free version...

[geniukas]

Thanks but thats the one I attached on my second post

[Glenn Kennedy]

No it isn't. the helpers are gone. Just the starting position and the final result are there.

[geniukas]

ogh yeh i see now cheers

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.