Formula to move data from rows to columns

Copper Contributor

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.

11 Replies

@sanjaylko 

 

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.

@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.

@sanjaylko 

As variant

image.png

in D11

=UNIQUE(A3:A21)

in E11

=TRANSPOSE(FILTER(B3:B21,A3:A21=D11))

and drag it down

@Sergei Baklan So true! Why didn't I think of that? :)

@Riny_van_Eekelen 

Since you like Power Query!

@Sergei Baklan True! And forgetting that many already might have all the new DA functions. But not all.

@sanjaylko 

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

@Riny_van_Eekelen of course, every tool has some limitations and it's own pros and cons

@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.

Thanks so much for your help. This worked and I have got the data in the required format. Only thing I could not understand was how to group rows. I had to finally copy your function and paste it. Then it worked. Really appreciate your help.
Thank you so much. This seems very simple. I will try it later today. Looking at the function it seems it will work beautifully. I am grateful.