Organizing data horizontally with pivot table instead of vertically

[wiz329]

Hi there,

I'll try to describe clearly what I'm looking for here, but it could be a little bit of a challenge!

Here's what my pivot table currently looks like (tabular format, no grand or subtotals, repeat items ON):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Atlanta[/TD]
[TD]Jim[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD]Caleb[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Nolan[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Abby[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Caroline[/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]Elizabeth[/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]Ben[/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]Mary[/TD]
[/TR]
</tbody>[/TABLE]

I want it to look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Atlanta[/TD]
[TD]Jim[/TD]
[TD]Caleb[/TD]
[TD]Anna[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]David[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Peter[/TD]
[TD]Nolan[/TD]
[TD]Abby[/TD]
[TD]Caroline[/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]Elizabeth[/TD]
[TD]Ben[/TD]
[TD]Mary[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this possible using a pivot table? I know I can do the same thing with complicated array formulas (http://www.mrexcel.com/forum/excel-...-data-provide-multiple-results-columns-2.html), but I would rather do this with a pivot table if possible.

 

[ExcelHelpsYou]

Would this work?

[/URL][/IMG]

 

[wiz329]

Would this work?

[/URL][/IMG]

Thanks for your reply! Unfortunately, I don't think this will work, as there needs to be exactly one row for every city.

The end goal in this scenario is to concatenate each of the names into a single cell. I actually ended up using the array formulas, but would love to know if there's an easy way to do this with pivot tables.

I could post a picture of the result, but I'm not sure how to post images! How did you attach yours?

 

[ExcelHelpsYou]

I uploaded my image to Photobucket. That let's you insert the URL of the image.

Personally, I don't like using PivotTables as a precedent for formulas. It would be great to see what you came up with though.

Nathan
ExcelHelpsYou

 

[Mike Szczesny]

Hi wiz329,
Another suggestion would to find the unique cities. You can use the frequency function for this. Then you can use an index function to find the related names.

 

[Fazza]

Instead of a pivot table, something a little similar is a query table. It can return a cross-tab result. Like a pivot table it doesn't use formulas and needs to be refreshed. It can readily handle huge amounts of data - which won't be the case using formulas, as they will be overloaded.

The description will seem a bit convoluted but this is just to try to explain it: it is a once-off set up & then just refreshed. For easier description, I'll assume the source data has headers "City" and "Name" and the full source data of headers & data is named (CTRL-F3) "MyData". (This isn't essential, just easier to explain. This isn't the only way to implement the approach.)

Save the file. ALT-D-D-N, Excel files, OK, browse for your Excel file, OK, on the LHS see YourData. Select either one or both fields, to show on the RHS. Hit next a few times til you get to the end. Select the option to view/edit in MS Query. Then the SQL icon, replace what you see by this

TRANSFORM MAX(C.Name)
SELECT C.City
FROM (
SELECT A.City, A.Name, COUNT(*) AS [TEMP]
FROM YourData A, YourData B
WHERE A.City = B.City AND A.Name >= B.Name
GROUP BY A.City, A.Name) C
GROUP BY C.City
PIVOT C.TEMP

OK to enter this. When you get a message about not being able to graphically represent it, OK to acknowledge and continue. See the result set in the GUI. Via the 'open door' icon exit MS Query & load the results set to a worksheet.

Regards, Fazza

 

[Fazza]

I had a 'brain fade' whilst replying, above. What started out as named range 'MyData' near the start of the explanation wrongly changed to a different name 'YourData' in the SQL. This won't work. to fix it, either (1) use "YourData" as the name for the source data, or, (2) leave the named range as "MyData", choose 'MyData' as the data source in the wizard, and change the two instances of YourData in the SQL to MyData. Hence,

TRANSFORM MAX(C.Name)
SELECT C.City
FROM (
SELECT A.City, A.Name, COUNT(*) AS [TEMP]
FROM MyData A, MyData B
WHERE A.City = B.City AND A.Name >= B.Name
GROUP BY A.City, A.Name) C
GROUP BY C.City
PIVOT C.TEMP