0 votes

am looking for transposing a table into another 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 another 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.

Mar 31, 2023 in Others 387 views

## 1 answer to this question.

0 votes

If you're using a previous version of Excel, you can copy the entire Company list and use the "Remove duplicates" tool, or you can enter a formula in A13 and drag it down until it's empty:

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

To get the 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 the sum of numbers in D13:

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

Result:

answered Mar 31, 2023 by
• 38,010 points

0 votes
1 answer

## How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

0 votes
1 answer

## How to merge columns in Excel but keep data on other columns

VLOOKUP indeed can be used here, combined ...READ MORE

0 votes
0 answers

## How to open a new tab or a new window while writing an exam in AI - Onlime Remote Proctored

How to open a new tab or ...READ MORE

0 votes
1 answer

## How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

0 votes
1 answer

## Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

0 votes
1 answer

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

0 votes
0 answers

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

0 votes
1 answer

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

0 votes
1 answer

## How to read an Excel CSV file in Python?

The csv module or the pandas library ...READ MORE

0 votes
1 answer

## How to get filtered value of an item as line chart which is absent from column chart in combined chart in Power BI?

By making a new measure that didn't ...READ MORE