As per sample excel attached. From original data to have output as intended, what's the best formula ?
I'm currently using a lot of helper column with INDAX MATCH formula, but it's infinite until match is found.
So the problem will be if the situation grow more than existing helper column, will need to manually add in columns and the formula calculation will becomes more hectic.
Looking for a simplified solution which not need to add any column manually.
One way:
=IFERROR(HSTACK(FILTER(A2:B8,C2:C8=""),FILTER(D2:E8,F2:F8="")),"")
ensure all results are deleted in result range first...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I don't see any formula in your Excel example
or (all in one) in N1:
=LET(a,HSTACK(FILTER(A2:B100,C2:C100="")&"",FILTER(D2:E100,F2:F100="")&""),VSTACK({"Current Ranking","Agent","Current Sponsor Rank","Current Sponsor"},FILTER(a,INDEX(a,,1)<>"")))
Dear Glenn.
Thanks a lot for the magic formula. Appreciate it very very much.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks