Multiple entry fields in pivot table- very urgent

dgondo

New Member
Joined
Dec 23, 2013
Messages
5
hi guys

So im working on large data sets with about 2000 entries. i want to be able to 'decompose' the multiple entry fields so that they fall in the right places ( with 1 count for 1 unique name) an example

my spreadsheet
Name Location for holiday
David bantry bay
Jonathan bantry bay, sea point, fresnaye
Joseph sea point, greenpoint
Jeremih sea point
Rosa greenoint
Chris greenpoint
Roy sea point, fresnaye

what the pivot table gives me

Location for holiday count
bantry bay 1
bantry bay, sea point, fresnaye 1
sea point, greenpoint 1
sea point 1
greenoint 2
sea point, fresnaye 1

what i would like to see

location for holiday count
bantry bay 2
sea point 4
fresnaye 2
greenpoint 2

i am dealing with large data sets with multiple variations in that particular "location for holiday" entry. Your help would be appreciated
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi

Is your table like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name Location for holiday [/TD]
[/TR]
[TR]
[TD]David bantry bay[/TD]
[/TR]
[TR]
[TD]Jonathan bantry bay, sea point, fresnaye[/TD]
[/TR]
[TR]
[TD]Joseph sea point, greenpoint[/TD]
[/TR]
[TR]
[TD]Jeremih sea point[/TD]
[/TR]
[TR]
[TD]Rosa greenoint[/TD]
[/TR]
[TR]
[TD]Chris greenpoint[/TD]
[/TR]
[TR]
[TD]Roy sea point, fresnaye[/TD]
[/TR]
</tbody>[/TABLE]

Or more like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 289"]
<tbody>[TR]
[TD="width: 289"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]Location for holiday [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]David[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]bantry bay[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Jonathan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]bantry bay[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]fresnaye[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Joseph[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]greenpoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Jeremih[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Rosa[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]greenoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Chris[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]greenpoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Roy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]fresnaye[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And does the result have to be a pivot table?


Per Erik
 
Upvote 0
This will copy data from columns A & B with possibility of multiple destinations in column B
To columns D & E with a single destination on each row.
Use those columns in your PT

Code:
Sub CreateMultipleLinesForMultipleDestinations()
    'Change A)Name
    '       B)Multiple Destinations
    'to
    'D)Name
    'E)Single Destination
    
    Dim lX As Long, lY As Long
    Dim lLastRow As Long
    Dim NextWriteRow As Long
    Dim varDest As Variant
    
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D1").Value = "Name"
    Range("E1").Value = "SingleDest"
    
    NextWriteRow = 2
    For lX = 2 To lLastRow
        If InStr(Cells(lX, 2), ",") > 0 Then
            varDest = Split(Cells(lX, 2).Value, ",")
            For lY = LBound(varDest) To UBound(varDest)
                Cells(NextWriteRow, 4).Value = Cells(lX, 1).Value
                Cells(NextWriteRow, 5).Value = varDest(lY)
                NextWriteRow = NextWriteRow + 1
            Next
        Else
            Cells(NextWriteRow, 4).Value = Cells(lX, 1).Value
            Cells(NextWriteRow, 5).Value = Cells(lX, 2).Value
            NextWriteRow = NextWriteRow + 1
        End If
   Next
End Sub
 
Upvote 0
Hi

Is your table like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name Location for holiday [/TD]
[/TR]
[TR]
[TD]David bantry bay[/TD]
[/TR]
[TR]
[TD]Jonathan bantry bay, sea point, fresnaye[/TD]
[/TR]
[TR]
[TD]Joseph sea point, greenpoint[/TD]
[/TR]
[TR]
[TD]Jeremih sea point[/TD]
[/TR]
[TR]
[TD]Rosa greenoint[/TD]
[/TR]
[TR]
[TD]Chris greenpoint[/TD]
[/TR]
[TR]
[TD]Roy sea point, fresnaye[/TD]
[/TR]
</tbody>[/TABLE]

Or more like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 289"]
<tbody>[TR]
[TD="width: 289"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]Location for holiday [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]David[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]bantry bay[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Jonathan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]bantry bay[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]fresnaye[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Joseph[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]greenpoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Jeremih[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Rosa[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]greenoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Chris[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]greenpoint[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 289"]
<tbody>[TR="class: grid"]
[TD="width: 289"]Roy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD="width: 75"]sea point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR="class: grid"]
[TD="width: 79"]fresnaye[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And does the result have to be a pivot table?


Per Erik

the result is more like

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]location of holiday[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]bantry bay[/TD]
[/TR]
[TR]
[TD]jonathan[/TD]
[TD]bantry bay, sea point, fresnaye[/TD]
[/TR]
[TR]
[TD]joseph[/TD]
[TD]sea point, green point[/TD]
[/TR]
[TR]
[TD]jeremih[/TD]
[TD]sea point[/TD]
[/TR]
[TR]
[TD]rosa[/TD]
[TD]greenpoint[/TD]
[/TR]
[TR]
[TD]chris[/TD]
[TD]sea point[/TD]
[/TR]
[TR]
[TD]roy[/TD]
[TD]sea point, fresnaye[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i know in the spreadsheet i can separate by using data and and then text to columns BUT it will be a problem when im working in the pivot table because the "location for holiday" will be under different headings and the data manipulation process will not end up correct ( the same problem is still there just in a diff way). I do need to work in a pivot table to manipulate the data to get the expected result
 
Upvote 0
hi,

the data is poorly set up

MUCH, MUCH easier if it is changed to


NAME, LOCATION
name1, location1
name1, location2
name1, location3
name2, location1
name3, location1
name3, location2

Then you might find a pivot table easily does what you want. If not, add a little SQL for things like

SELECT DISTINCT NAME, LOCATION
FROM YourData

SELECT LOCATION, COUNT(*)
FROM YourData
GROUP BY LOCATION

etc, etc
 
Upvote 0
i am going to test that out. the only other thing is that those aren't the only 2 fields i am dealing with. there are also other fields like date captured, Max spending price, Type of accommodation wanted, # of bedrooms etc which are the key parts id want to manipulate using the pivot table. so ud find that what id want in the pivot table would be something like

dates
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]location for holiday[/TD]
[TD]Nov 2013[/TD]
[TD]avg spending price[/TD]
[TD]dec 2013[/TD]
[TD]avg spending price[/TD]
[TD]jan 2013[/TD]
[TD]avg spending price[/TD]
[/TR]
[TR]
[TD]sea point[/TD]
[TD]1[/TD]
[TD]R5000[/TD]
[TD]2[/TD]
[TD]R15000[/TD]
[TD]1[/TD]
[TD]R2500[/TD]
[/TR]
</tbody>[/TABLE]

+ other manipulations with more pivot tables. is it then possible to still "decluster" the location of holiday and still get the above result
 
Last edited:
Upvote 0
continuation of previous post

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location for holiday[/TD]
[TD]Max Spending price[/TD]
[TD]Type of accomo[/TD]
[TD]# of bedrooms[/TD]
[TD]Number of peopl[/TD]
[TD]date of holiday[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]bantry bay[/TD]
[TD]6000[/TD]
[TD]lodge[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]nov 2013[/TD]
[/TR]
[TR]
[TD]roy[/TD]
[TD]sea point, fresnaye[/TD]
[TD]5000[/TD]
[TD]lodge[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]nov 2013[/TD]
[/TR]
[TR]
[TD]chris[/TD]
[TD]sea point[/TD]
[TD]18000[/TD]
[TD]penthouse[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]dec 2013[/TD]
[/TR]
[TR]
[TD]rosa[/TD]
[TD]greenpoint[/TD]
[TD]8000[/TD]
[TD]apartment[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]dec 2013[/TD]
[/TR]
[TR]
[TD]jeremih[/TD]
[TD]sea point[/TD]
[TD]12000[/TD]
[TD]penthouse[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]dec 2013[/TD]
[/TR]
[TR]
[TD]joseph[/TD]
[TD]sea point, greenpoint[/TD]
[TD]2500[/TD]
[TD]lodge[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]jan 2013[/TD]
[/TR]
[TR]
[TD]jonathan[/TD]
[TD]bantry bay, sea point, fresnaye[/TD]
[TD]7000[/TD]
[TD]hotel[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]jan 2013

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Looks like a few tables would be needed for an ideal set up. Then you'd have to work like a database & relate/join tables to each other. (You might even find it easier to work in MS Access as it fundamentally is easier to work with this sort of data.) This is not as simple as a single table in Excel.

For info on data normalisation, refer for example http://en.wikipedia.org/wiki/Database_normalization

And http://support.microsoft.com/kb/283878

And google for further info if interested.

If you don't have much to do, then you might get away with a less than ideal set up (to do only what you need and not worry about being well structured). If it is only a one off exercise then maybe just do whatever you need however you can.

hth
 
Upvote 0
One other problem is when the price is associated with multiple locations you cannot tell how much of the price goes with each location. Is this a problem in the way you are using the information? My earlier code could be easily modified to copy the other columns and either evenly divide the cost, or leave the same cost in all three areas.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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
Back
Top