Feb 05 2021 01:48 AM
I have the data as shown in TABLE 1 of the attached file. I need to display it as shown in TABLE 2. Is there a formula one can use for this purpose? The sample file uploaded is small so one can filter, copy cells and transpose but I have files which have around 50K rows.
There is some urgency to finding a solution to my problem. Any help is deeply appreciated.
Feb 05 2021 02:22 AM
Here is an example file with two options with VBA.
Only the details should be better adapted to your needs,
I would be happy to know if I could help.
I wish you continued success with Excel
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.
Feb 05 2021 03:53 AM
@sanjaylko I would suggest you look into using PowerQuery. Are you familiar with that?
Attached file has a simple query that creates up to 10 columns with amounts, but that can be changed easily. PQ connects to the data in your Table1. Note that I named it "NamesAndAmounts".
As long as your real list has two columns with the same names and you create a structured table from it (Ctrl-T) with the same name as above, the query should work.
Let me know if you run into any problems.
Feb 05 2021 09:29 AM
As variant
in D11
=UNIQUE(A3:A21)
in E11
=TRANSPOSE(FILTER(B3:B21,A3:A21=D11))
and drag it down
Feb 05 2021 09:32 AM
@Sergei Baklan So true! Why didn't I think of that? :)
Feb 05 2021 09:42 AM
Since you like Power Query!
Feb 05 2021 09:44 AM
@Sergei Baklan True! And forgetting that many already might have all the new DA functions. But not all.
Feb 05 2021 09:45 AM
I'm sorry, actually second formula shall be with absolute references
=TRANSPOSE(FILTER($B$3:$B$21,$A$3:$A$21=D11))
As a comment, it's much better to use structured tables or at least dynamic ranges
Feb 05 2021 09:46 AM
@Riny_van_Eekelen of course, every tool has some limitations and it's own pros and cons
Feb 05 2021 06:55 PM
@NikolinoDE Thanks so much for your help. There are minor issues with the VBA. It is not giving the entire output but this has got me thinking and points me in the right direction.
Feb 05 2021 06:57 PM
Feb 05 2021 07:02 PM