Values In A Column Causing Duplicate Rows (When Exporting A Query Into Excel)

punkasf

New Member
Joined
Mar 15, 2015
Messages
33
Hello all, I am looking for some guidance on how to handle a (SQL) query that is causing duplicate rows due to one column's values. In the example below, the data from the "Name," "Age" and "City" columns is stored in a main source while the date for "Fruit" and "Quantity" are being pulled from a separate source and is not stored properly in the main source, causing extra rows with mostly duplicate data when the query combines these fields. As you can see from the tables below, I am looking to take the three distinct values from the "Fruit" column and have each be their own columns using the values of the "Quantity" column, thus making the table much easier to read.

Ideally, I would look into the source or SQL code to take care of this but, as a user, my organization limits my access at this level so I am looking at how to handle this when exporting the information to Excel. What is this problem/technique called and what are some strategies to work around it? I have tried several subtle changes when searching this issue but all the solutions deal with deleting duplicate rows, which is what I want to do, but also transform part of the table. Is there a built-in capability in Excel that already deals with this that I am ignorant about?

NameAgeCityFruitQuantity
Tom
20​
BelfastCherry
6​
Tom
20​
BelfastGrape
1​
Tom
20​
BelfastHoneydew
4​
Harry
31​
EdinburghCherry
8​
Harry
31​
EdinburghGrape
0​
Harry
31​
EdinburghHoneydew
2​
Mary
29​
DublinCherry
3​
Mary
29​
DublinGrape
8​
Mary
29​
DublinHoneydew
1​


NameAgeCityCherryGrapeHoneydew
Tom
20​
Belfast
6​
1​
4​
Harry
31​
Edinburgh
8​
0​
2​
Mary
29​
Dublin
3​
8​
1​
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Sounds like you need a Pivot Table.


1623808727687.png
 

Forum statistics

Threads
1,147,518
Messages
5,741,638
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top