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 Pro, Projects, Mentor, TrainerAsked:
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.

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
RomoloIT Pro, Projects, Mentor, TrainerAuthor 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
As I asked previously, please attach a workbook
0
RomoloIT Pro, Projects, Mentor, TrainerAuthor 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 Pro, Projects, Mentor, TrainerAuthor 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

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
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
What I found is the best solution as no other solutions supplied match the requirements and not much info received.
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
VBA

From novice to tech pro — start learning today.