How to transpose data in an other tab in excel

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 by narikkadan
• 63,720 points
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:

enter image description here

answered Mar 31, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

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

answered Jan 22, 2023 in Others by narikkadan
• 63,720 points
370 views
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

answered Nov 19, 2022 in Others by narikkadan
• 63,720 points
775 views
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

Jul 20, 2020 in Others by vimalkamal
• 120 points
3,466 views
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

answered Sep 24, 2022 in Others by narikkadan
• 63,720 points
1,078 views
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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,531 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,687 views
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

Feb 17, 2022 in Others by Edureka
• 13,670 points
830 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,024 views
0 votes
1 answer

How to read an Excel CSV file in Python?

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

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
655 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP