Semi automatic way to change data structure on excel or others tools?

Question

Any idea how to convert this structured data:

into the following?

I have 30K rows so I would need a semi automatic way to do it if possible.

Thanks for your time

UPDATE:

What is your excel version? – Harun24hr Jun 25 '21 at 08:49 — Harun24hr, Jun 25 '21 at 08:49
Excel for Mac 16.50 – Judit Casacuberta Bagó Jun 25 '21 at 12:41 — Judit Casacuberta Bagó, Jun 25 '21 at 12:41

score 0 · Accepted Answer · answered Jun 25 '21 at 09:15

0

As per my below screenshot I have used below formula to B6 cell to extract unique numbers.

=TRANSPOSE(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,B2:H4)&"</s></t>","//s")))

Then in B7 cell use SUMPRODUCT() to find per client number is exist or not and copy down & across the formula as needed.

=SUMPRODUCT(($A$2:$A$4=$A7)*($B$2:$H$4=B$6))

answered Jun 25 '21 at 09:15

Harun24hr

This would be much more intersting if you could offer a formula that Spills the counts. Perhaps something based on [this](https://stackoverflow.com/a/66299104) – chris neilsen Jun 25 '21 at 09:28
I tried the formular but I'm getting a Name error on B6. I attached the screenshots on my original question as I don't know how to add images or files here. – Judit Casacuberta Bagó Jun 25 '21 at 12:38
removing the first formula (B6) it works for me. I can do that one manually. Thanks!! :) – Judit Casacuberta Bagó Jun 25 '21 at 12:48

1 Answers1