2

I am looking for transposing a table into an other tab in Excel.

My first table :

First_Name | Last_Name | Company   | Number  | Done (1 = yes / 0 = no)
-----------------------------------------------------
Allison    | Dark      ! McDonald  | 2,00    |  1
John       | Doe       | Amazon    | 10,00   |  1
Julian     | Smith     | Coca Cola | 3,00    |  0
Kathy      | Johnson   | Coca Cola | 20,00   |  1
Barbara    | Brown     | Tesco     | 10,00   |  0
Alexander  | Lee       | Amazon    | 4,00    |  0
Harry      | Moore     | Amazon    | 8,00    |  0

How can I retrieve these data in an other tab like this:

Company   | Number of names | Done   |  Sum of Numbers 
------------------------------------------------------
Amazon    |     3           | 1 on 3 |   22,00
Coca Cola |     2           | 1 on 2 |   23,00
McDonald  |     1           | 1 on 1 |   2,00
Tesco     |     1           | 0 on 1 |   10,00

I was looking for a formula but it also can be in vba. Thanks for your help.

made leod
  • 85
  • 7
  • 3
    This is a perfect scenario for the use of a pivot table. – MadMarc Mar 30 '23 at 15:45
  • What did you try? As a start, for column A you can use UNIQUE, for column B COUNTIF, same for C. And SUMIF for D. Or pivot table as @MadMarc says – user11222393 Mar 30 '23 at 15:50
  • Thx for these tips. I cannot use UNIQUE because of my old version. And yes, I could use Pivot Table but I was wondering how I can do it without pivot table but a formula with LOOKUP for instance. – made leod Mar 30 '23 at 16:08

3 Answers3

2

Assuming no Excel version constraints as per the tags listed in the question. This is another application of the REDUCE/VSTACK pattern, you can check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length. The formula spills the entire result including the header and returning the result sorted by company name. Put on G1 the following:

=LET(in, A1:E8, h, TAKE(in,1), data, DROP(in,1), cmp, INDEX(data,,3),ux, 
 SORT(UNIQUE(cmp)), done, INDEX(data,,5), amnt, INDEX(data,,4), 
 header, {"Company","Number of Names","Done","Sum of Numbers"}, CALC, LAMBDA(x, 
 LET(a, cmp=x, b, SUM(N(a)), c, SUM((a)*(done=1)), 
 HSTACK(x, b, c&" on "&b,SUM(a*amnt)))), REDUCE(header, ux, LAMBDA(ac,x, 
 VSTACK(ac, CALC(x)))))

Note: Updated the formula looking at @VBasic2008 I realized the companies can be sorted first, which allows for reducing the formula.

Here is the output: output

The user LAMBDA function CALC does the calculation to generate an entire row on each iteration of REDUCE. We can not use the header to initialize the accumulator (ac), because we need to sort the result first.

David Leal
  • 6,373
  • 4
  • 29
  • 56
1

With older versions of Excel to get unique list you may copy all Company list and use excel function "Remove duplicates" or place formula in A13 and drag it down till it gets empty:

=IFERROR(INDEX($C$2:$C$8, MATCH(0,COUNTIF($A$12:A12, $C$2:$C$8), 0)),"")

To get company name count paste in B13 and drag down:

=COUNTIF($C$2:$C$8,A13)

For "Done" in C13:

=COUNTIFS($C$2:$C$8,A13,$E$2:$E$8,1)&" of "&COUNTIF($C$2:$C$8,A13)

For sum of numbers in D13:

=SUMIF($C$2:$C$8,A13,$D$2:$D$8)

Result:

enter image description here

Adapt ranges to your data and new table destination.

user11222393
  • 3,245
  • 3
  • 13
  • 23
1

Transform Data (Excel Formula)

Improved (Edit)

  • After applying David Leal's suggestions in the comments, it looks like this:
=LET(d,C2:E8,del," on ",dCols,3,
    co,TAKE(d,,1),u,SORT(UNIQUE(co)),
HSTACK(u,MAKEARRAY(ROWS(u),dCols,LAMBDA(r,c,
    LET(cc,MOD(c,dCols)+c-1,f,co=INDEX(u,r,1),
        fr,ROWS(FILTER(co,f)),s,SUM(INDEX(d,,cc)*f),
IF(cc=1,fr,IF(cc=2,s,s&del&fr)))))))

enter image description here

Initial

=LET(d,C2:E8,del," on ",
    co,TAKE(d,,1),u,SORT(UNIQUE(co)),
HSTACK(u,MAKEARRAY(4,3,LAMBDA(r,c,
    LET(cc,MOD(c,3)+c-1,f,co=INDEX(u,r,1),
IF(cc=1,ROWS(FILTER(co,f)),
     LET(s,SUM(FILTER(INDEX(d,,cc),f)),
IF(cc=2,s,s&del&ROWS(FILTER(co,f))))))))))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • This is an interesting approach and avoids using `REDUCE/VSTACK`, which may be not efficient for very large data. I need to get familiar with the `MAKEARRAY` alternative. – David Leal Mar 31 '23 at 13:44
  • @DavidLeal I try to avoid it when possible. I'm not quite sure about `ROWS(FILTER(co,f))`. Would it be better placed in the 2nd `LET` (it's only used for columns 1 and 3)? – VBasic2008 Mar 31 '23 at 13:50
  • I thinks it is possible, the following is a shorter approach under the same idea: `=LET(d,C2:E8,del," on ",co,TAKE(d,,1),u,SORT(UNIQUE(co)), HSTACK(u,MAKEARRAY(ROWS(u),3,LAMBDA(r,c, LET(cc,MOD(c,3)+c-1,f,co=INDEX(u,r,1), cnts, ROWS(FILTER(co, f)), s, SUM(INDEX(d,,cc)*f), IF(cc=1,cnts, IF(cc=2,s,s&del&cnts)))))))` I get the same result. I use `ROWS(u)` instead of `4` – David Leal Apr 01 '23 at 00:06
  • 1
    @DavidLeal I knew an intervention was needed. Thanks for the suggestions. The 4 was really a ridiculous mistake but getting rid of FILTER, that one I have to memorize. I implemented all of it. – VBasic2008 Apr 01 '23 at 03:06