Using Lookups/Index/Match on Concatenated / Merged Data

I currently have a set of repeating data that is being concatenated and submitted to a flat-database (SharePoint list) the result of the data looks like "A; B; C;" etc... there are also potentially blanks if not all the fields are filled out ala "; ; ; ; ;" typically at the end, but really could be anywhere in the concatenated string.

I would like to do some lookups on this data in excel but I'm not sure what the best way to go about it would be?
I've attached a spread sheet to show you an example of the lookup I'd like to do....

PS the data has to be merged/concatenated because we are using a SharePoint list; however, if you have a suggestion on a different way of delimiting the data so that it is easier to clean let me know.
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
The attachment is missing..Can you please update the same...

Saurabh...
0
-PolakAuthor Commented:
Here you go sorry!
Concatenated-Data-Example.xlsx
0
Saurabh Singh TeotiaCommented:
Can you help me a bit to understand the logic of Concatenated Data and what you are trying to do here?? As looking at workbook i'm not sure about what you are trying to do
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

-PolakAuthor Commented:
Did you see Sheet2? Basically I would like a value returned for the color on Sheet1 depending on the combination of letters.

For example, If the only letter in the Concatenated String was "A; "

Then the return for the Purple and Yellow colors would be: 1 for Purple  and 1 for Yellow the rest of the Colors would have a value of 0 per the table on Sheet2.
0
Saurabh Singh TeotiaCommented:
Now in your example you have

A; B; C; D; E; F; G; H; I; J; K; ; ; ;

In the above example do you want to pick value only for A or pick values of A,B,C,D,E,F,G,I,J,K? and if you want to pick all the values of that do you want to add those values in color or what you want to do??

Saurabh...
0
-PolakAuthor Commented:
"A; B; C; D; E; F; G; H; I; J; K; ; ; ;" is just a test for your lookup/solution.

The data can have any combination of all of those letters... for example row 3 has "D; C; B; A; F"

PS please delete column M on sheet1 that was a copy & paste oversight.
0
Saurabh Singh TeotiaCommented:
Polak,

I'm confused here and the reason for the same that your sheet-1 has unique id-->1AER@
ASDF2
SDF23

Where as your sheet2 values has A,B,C,D,E,F so i'm not sure how you doing the lookup/solution...as the lookup solution is based upon the fact that their is something common between both the worksheet and apart then the color i don't see anything which is common in sheet1 and sheet2..Can you please provide me a clarity upon how do you want to do this lookup??

Saurabh....

K
0
-PolakAuthor Commented:
Unique ID has nothing to do with the Lookup.... Sorry I can see how that was confusing... Unique ID was just my way of saying "row2 is a unique record", "row3 is a unique record", etc..

The lookup should be done using the Letters in the "Concatenated Data" column and the Colors.

The Letters and Colors are common across both sheets... The letters are just Concatenated.. hence my problem and question....
0
Saurabh Singh TeotiaCommented:
Is this is what you are looking for..?? I'm assuming you want the lookup of the first value in your concatenated data...

Saurabh...
Concatenated-Data-Example-1.xlsx
0
-PolakAuthor Commented:
This is what I want for all the letters in a concatenated string....Let me put this a different way...

If the Letters were in separate columns rather than concatenated this would be easy. But because they are concatenated, based on how the data is collected, I think need a lookup that somehow incorporates the ";" delimiter to figure out what combination of letters are in a unique record and then returns the respective values across the various colors.
0
Saurabh Singh TeotiaCommented:
That's what i asked you..now when you say respective values...how do you want to show those respective values..?? Want to add them up?? Can you show me an example of how do you want to show values for the 1st row?? as thats where i'm confused about...
0
-PolakAuthor Commented:
Yes a sum, here is an edited workbook that shows what Lookup would return for Row 3:

Row2 would literally be the totals row on sheet2. I just wanted to test the solution and see if it works in the case of "; " blanks because that can happen with how users submit the data to SharePoint.
Concatenated-Data-Example2Row3Solved.xls
0
-PolakAuthor Commented:
Also if it helps you a letter can only appear ONCE.
Ala, You would never have:
"A; A; A;"
0
Saurabh Singh TeotiaCommented:
Their you go..I wrote a UDF for you which does what you are looking for...It works similar to sumif only.. In the last option give the delimiter which you want to use as a separator...

This will do what you are looking for...

Saurabh...
Concatenated-Data-Solved.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
There we go! Glad we finally got on the same page. Tested and works. Thank you!

Do you know if there is any better way to delimit the data to make it more palatable for excel to handle? I can still control that at this point because the collection system is in Beta.
0
Saurabh Singh TeotiaCommented:
You can use any delimiter and simply change that in the formula it will adjust that as well..Few common delimeters used in excel are:-

Comma , Semi Collon or Pipe sign..I'm fine with anything you use as excel can handle that and even this formula will be able to handle as well..
0
-PolakAuthor Commented:
Thanks Again
0
Saurabh Singh TeotiaCommented:
You are welcome...Glad to help.. :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.