Multiple entry fields in pivot table- very urgent

[dgondo]

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

 

[perimidt]

Hi

Is your table like this:

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

<tbody>
</tbody>

Or more like this:

Name <tbody> </tbody>	Location for holiday <tbody> </tbody>
David <tbody> </tbody>	bantry bay <tbody> </tbody>
Jonathan <tbody> </tbody>	bantry bay <tbody> </tbody>	sea point <tbody> </tbody>	fresnaye <tbody> </tbody>
Joseph <tbody> </tbody>	sea point <tbody> </tbody>	greenpoint <tbody> </tbody>
Jeremih <tbody> </tbody>	sea point <tbody> </tbody>
Rosa <tbody> </tbody>	greenoint <tbody> </tbody>
Chris <tbody> </tbody>	greenpoint <tbody> </tbody>
Roy <tbody> </tbody>	sea point <tbody> </tbody>	fresnaye <tbody> </tbody>

<tbody>
</tbody>

And does the result have to be a pivot table?


Per Erik

 

[pbornemeier]

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

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

 

[dgondo]

Hi

Is your table like this:

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

<tbody>
</tbody>

Or more like this:

Name <tbody> </tbody>	Location for holiday <tbody> </tbody>
David <tbody> </tbody>	bantry bay <tbody> </tbody>
Jonathan <tbody> </tbody>	bantry bay <tbody> </tbody>	sea point <tbody> </tbody>	fresnaye <tbody> </tbody>
Joseph <tbody> </tbody>	sea point <tbody> </tbody>	greenpoint <tbody> </tbody>
Jeremih <tbody> </tbody>	sea point <tbody> </tbody>
Rosa <tbody> </tbody>	greenoint <tbody> </tbody>
Chris <tbody> </tbody>	greenpoint <tbody> </tbody>
Roy <tbody> </tbody>	sea point <tbody> </tbody>	fresnaye <tbody> </tbody>

<tbody>
</tbody>

And does the result have to be a pivot table?


Per Erik

the result is more like

name	location of holiday
david	bantry bay
jonathan	bantry bay, sea point, fresnaye
joseph	sea point, green point
jeremih	sea point
rosa	greenpoint
chris	sea point
roy	sea point, fresnaye

<tbody>
</tbody>

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

 

[dgondo]

hi Phil

I tried it but it still doesn't give me what i am looking for

 

[Fazza]

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

 

[dgondo]

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

location for holiday	Nov 2013	avg spending price	dec 2013	avg spending price	jan 2013	avg spending price
sea point	1	R5000	2	R15000	1	R2500

<tbody>
</tbody>

+ other manipulations with more pivot tables. is it then possible to still "decluster" the location of holiday and still get the above result

 

[dgondo]

continuation of previous post

Name	Location for holiday	Max Spending price	Type of accomo	# of bedrooms	Number of peopl	date of holiday
david	bantry bay	6000	lodge	2	4	nov 2013
roy	sea point, fresnaye	5000	lodge	2	4	nov 2013
chris	sea point	18000	penthouse	3	6	dec 2013
rosa	greenpoint	8000	apartment	3	3	dec 2013
jeremih	sea point	12000	penthouse	2	3	dec 2013
joseph	sea point, greenpoint	2500	lodge	1	1	jan 2013
jonathan	bantry bay, sea point, fresnaye	7000	hotel	1	2	jan 2013

<tbody>
</tbody>

 

[Fazza]

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

 

[pbornemeier]

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.