all! I couldn't find an answer that covers all my requirements, or at least I wasn't looking for the right terminology. Hopefully this is something that's possible. I'd like to use a formula for this if possible but if it needs to be a macro then so be it. I'll try my best to explain the problem. Fake values are used to protect workplace data. It's Excel 2007 and I don't have permission to use any add-ons.
I have two sheets, let's call them Source and Target. The values on these sheets change constantly but the layout remains mostly the same. Source A1:B1 are just headings. Let's say A1 is Item and B1 is Client. Item has a unique value in each row. They never repeat. They are always in alphabetical order. Client does repeat and is in no particular order. Clients and Items are added/deleted all the time.
EXAMPLE SOURCE:
Target's top row are for headings. Of these headings, A1 is Client and that column lists each of the Clients once each in alphabetical order. Clients are added/removed from both sheets frequently.
EXAMPLE TARGET:
Now with that all out of the way, this is what I need to do: I want some way for Target to automatically grab Items from Source and place them in the appropriate row on Target. So far I haven't been able to get it to recognize an Item that it already grabbed.
Use this formula:
=IFERROR(INDEX(Source!$A:$A,SMALL(IF($A2=Source!$B:$B,ROW(Source!$B:$B),99999999),COLUMN(A2))),"")
Array Formula press Ctrl+Shift+Enter at the same time
in B2 under the first item in Target
next to the first client
You can drag it in the row and in the column