PIVOTTABLE: Group relevant data from across multiple rows under a single row label.

Hello,

With the way you have the data I could not provide the PIVOT TABLE the way you want. I don't even think it's possible due to what you have said - both names are in the same row and therefore don't quite know if there's a wayout of that situation without changing the way data is shown.


One thing I do know - if you display the data in other way you could be able to do the PIVOT TABLE.

I have attached an example.


I know that probably it's not quite the answer you would like to read, but let's see if some other users (with more experience) have a look at this and get a better solution.



Kind Regards,
FCarv

FCarv, thanks a lot for chipping in. See, I don't mind an intermediary table or PivotTable that first summarizes the data by name, but what I can't change is the original data table, because it is the way users input the data in the most streamlined way.

How many tables or transpositions of the original data will be needed is not that much of a concern, since they can be hidden from the user. But the solution, however, must allow for dynamic input of new data. In other words, I can't pause, copy/paste ranges to new sheets, transpose and re-analyze everything every time I want a report. This is why a final report in the form of a PivotTable that can be dynamically refreshed as new data gets entered is the optimal solution.

Btw, can you think of a way I can create an intermediary table like the second one in your example that can be updated automatically?

Yes, most certainly you can.
I'll try to get back to you later on, hopefully with a solution.

What I will try to do basically is the following:

1. Create a macro that opens that file where users input the data, copy that same data, closes that file without saving changes, and then paste in the newly created workbook (Active)
2. Changes the way the data is displayed into an alternative way
3. Inserts a PIVOT TABLE

If, meanwhile someone bumps into this topic and, having more experience with macros, decide to help you out, even better.
Because with me it may take a while


Rgds,
FCarv

Ok, it's not yet done, but I have already done the first part.

In what regards the second part I will require some help in order to transpose the data from Sheet "User Input Data File" to Sheet "Report". I'm having dificulties displaying the data like that.

Attached are the following files:
Test.xlsx (It's the file where users input the data)
Report.xlsx (it's the file I did create in order to help you)
Example.xlsx

So basically, you open this Report book and at "Main" Sheet press button Extract and open the Test book. This will copy the data from there to this book and name the sheet "User Input Data File".

Then you go to "Main" sheet again and press button "Get Report" and that runs the second part, which will be transpose the data from the way it is to the way we desire and do a pivot table.


Unfortunately my knowledge in VBA doesn't allow me to go further in step 2.
Can someone help with the code to do the second part (it can be only the transpose part).

We require the data to be like the one shown in the Example book.


Rgds,
FCarv

@FCarv,

Thanks a lot for your work!

Your suggestion lead me to another strategy: combine the "To" and "From" columns into a single column, remove duplicate values and use SUMIF or another lookup function to extract and sum corresponding data in separate columns.

This way value totals for each name will update themselves automatically, but I still can't figure out how to make the combined 'Name' column update itself dynamically when a new name is entered without using a macro.

If someone can solve this, I'd be really grateful!

Please look at the attached sheet.

Thanks.