• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Offset to concatenate data

I've been trying to come up with a way using either formula, power query, or VBA, to determine work by primary location. The attached has a table with client numbers, locations, and % of work for those locations for the last 5 years (2013-2018 - all separate columns). I would like to find a way to produce the data in column P without the use of too many helper columns. With the addition of several columns, I can use an index/match/offset combo, and then combine the text from there, but that gets difficult to read and can sometimes be confusing. So I am looking for other solutions. I am very comfortable with formulas, moderately comfortable with Power Query, and a little comfortable with VBA, so I am trying to find solutions in that order. But any solution that makes combining this text easier, I will totally work with it. I feel like there's an obvious solution I'm completely overlooking, but I've been playing around with this for a couple of hours and nothing else has come to me.

Thanks in advance,
YL
 

Attachments

  • Testing Offset.xlsx
    313 KB · Views: 4
N2: =INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0))&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)),"0.0%")&"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+1)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+1),"0.0%")

copy down
 
This allows for the case where there is only 1 match

N2: =INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)) & "-" & TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)),"0.0%") & IF(INDEX(Location[CLIENT],MATCH(L2,Location[CLIENT],0)+1)<> INDEX(Location[CLIENT],MATCH(L2,Location[CLIENT],0)),"", "/" & INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+1) & "-" & TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+1),"0.0%"))
 
Thanks so much! I can certainly work with this! There are a few times where the client number is listed 3 times, or just once, but I can use the helper column of number of offices and use an if/then formula around this. Somehow I missed out on learning the use of row/+1 with the index/match formula!

Thank you!!
 
this allows up to 6 which is the max in the list

N2:
=INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0))&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)),"0.00%")& IF(M2>1,"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+1)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+1),"0.00%"),"") & IF(M2>2,"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+2)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+3),"0.00%"),"")& IF(M2>3,"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+3)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+3),"0.00%"),"")& IF(M2>4,"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+4)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+4),"0.00%"),"")& IF(M2>5,"/"&INDEX(Location[LOCATION],MATCH(L2,Location[CLIENT],0)+5)&"-"&TEXT(INDEX(Location[2017 Unique],MATCH(L2,Location[CLIENT],0)+5),"0.00%"),"")
 
Another option if you didn't have TEXTJOIN function (Excel2016), and try this with helper column way.

1] In Helper O2, copied down until blank :

=IF(A2="","",IF(I2=0,"",IF(A2=A3,B2&"-"&TEXT(I2,"0.00%")&IF(O3="","","/")&O3,B2&"-"&TEXT(I2,"0.00%"))))

2] In N2, copied down :

=IF(L2="","",VLOOKUP(L2,A$2:O$982,15,0))

3] See attachment.

Regards
Bosco
 

Attachments

  • Testing Offset(1).xlsx
    335.5 KB · Views: 5
Back
Top