Append text to results of vlookup or sumif equivalent in Excel

I need to do a find like a sumif or vlookup and append the results into a string within MS Excel.

I have data that appears within a table and I have SUMIFS working now on the number element. However, there is a column that has a text reference which I need to harvest from the list for the matches (like a SUMIF) and display them as a result against my result sheet.

So would get

Dan         80 hours (made up of 4 finds in the table)       ABC123, Z345, LK8, 98KJ (reference made up of the 4 finds discussed earlier).

Apart from creating a VBA statement to run on each criteria (Dan, Mike, John etc...) and searching and building an array possibly, cannot think of another solution.... And that would take some work from me.

Any ideas please?

Many thanks in advance

R
LVL 7
RomoloIT Professional ConsultantAsked:
Who is Participating?
 
RomoloIT Professional ConsultantAuthor Commented:
I have used INDEX to get what I needed. And using the ROW function. Across several columns/concatenation.
Thanks for your input everyone.
0
 
Roy CoxGroup Finance ManagerCommented:
An example workbook with some dummy data and expected result would help.
0
 
Rob HensonFinance AnalystCommented:
Sounds like you might be able to use a Pivot Table.

If I understand the question correctly, with a pivot you could end up with a result that looks like:

Dan
   ABC123  20
   Z345       15
   LK8         35
   98KJ       10
Dan Total  80
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RomoloIT Professional ConsultantAuthor Commented:
Hi peeps,

I am doing this over 200 different people in a spreadsheet with possibly one occurrence, no occurrences or 5 occurrences.

So data would be


Dan      80     ABC
Mike     70     OIU
Alan      25     ABC
Mary     100   XYZ
Dan        35    TRY
Joe         75     XYZ
Lou        89     UYT
Joe         36     ABC

So I need something that will iterate through or allow me to do a SUMIF which I have done with the numbers but itstead of it being an aggregate it appends. The result would be 200 peoples name with a total against it (thats all done) and then a reference with numerous occurrences such as:

Dan      115     ABC, TRY
Mike     70     OIU
Jeremy  0
Alan      25     ABC
Mary     100   XYZ
Joe         110     XYZ, ABC
Lou        89     UYT

Please advise folks.

Thanks in advance


R
0
 
Roy CoxGroup Finance ManagerCommented:
As I asked previously, please attach a workbook
0
 
RomoloIT Professional ConsultantAuthor Commented:
Here you go... in Excel form.

I was considering a pivot table and having it auto refresh with vba and then trying to do a vlookup to the table against my list.

However, was wondering if anything better from anyone?

Thanks
SampleFile.xlsx
0
 
RomoloIT Professional ConsultantAuthor Commented:
What I found is the best solution as no other solutions supplied match the requirements and not much info received.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.