Sorting after Pivot Table

[Giles Dickenson]

After creating a pivot table I need to further sort the data to find groups that have variations of the various columns in common.

See attached file - Col A is a user with possibly multiple entries in Col F (type of connection) (the actual file has 120,000+ rows of data with 20 different variables in Col B too). The first row of the pivot table has 5 entries in various columns (the second row has 4 entries) - how many times does the same grouping occur, what is the grouping and how many groupings are there?

So far I've only come up with manual filters as a solution which is fine for one or two lines but doesn't scale for a large amount of data.

Thanks for any ideas!

 

[NARAYANK991]

Hi ,

What is the precise definition of a group in terms of worksheet cells , and what would constitute a grouping difference ?

Narayan

 

[Giles Dickenson]

Thanks for your question. I'm not sure of the best way to describe my problem!

If you look at the Pivot table tab of the spreadsheet there is one row for each user. On that row there are possible entries in various columns depending on the connection types for that user. The group for each user is potentially any combination of connection types.

Row 1 - user 0002400000 has a group of 101, REPR, VL, W-RES & WW-RES.
Row 2 - user 0003510000 has a group of 101, REPR, W-RES, WW-RES.
Row 3 - user 0006186584 has a group of 101, REPR & WW-FC.

While there are possibly a very large number of variations in practice lots of users have the same combinations of connections - I'd like to sort the results to find out how many have the same group of connections.

Please let me know if that's not clear.

 

[NARAYANK991]

Hi ,

Somewhat clear , thanks.

What would be the minimum number of items to form a group ?

Suppose some row somewhere in the data had only 101 ; now would that alone form a group ?

If so , then we would have to go through all the possible combinations of one or more items from some maximum number of items ; what would that maximum number be ?

Narayan

 

[Giles Dickenson]

Hi ,

Somewhat clear , thanks.

What would be the minimum number of items to form a group ?

Suppose some row somewhere in the data had only 101 ; now would that alone form a group ?

If so , then we would have to go through all the possible combinations of one or more items from some maximum number of items ; what would that maximum number be ?

Narayan

The maximum would be 50 (total number of possible connection types)
The minimum is 1, but there would be multiple different groups of 1.

In practice most groups would be 5-6 or less.

 

[NARAYANK991]

Hi ,

I am not sure that a formula solution is possible , in that putting a formula in automatically brings all of the users together. I may be wrong.

What I have done is :

1. Sort the data on column A and column F , so that all data pertaining to a user is together , and in the same order for every user. This is because the values in column F were in different order for different users ; we would then have one group of 101 , 108 , while another user would have 108 , 101. Identifying that these were the same was eliminated if we sorted the data first.

2. to concatenate all values in column F for each user in column H.

3. Next , I did a copy , Paste Special , Values in column H so that the data could be sorted on this column.

4. Next , the result was filtered to hide blanks.

You can see that for every group , all users having that group have been put together.

See if this can be replicated in your working file.

Narayan

 

[Giles Dickenson]

Hi ,

I am not sure that a formula solution is possible , in that putting a formula in automatically brings all of the users together. I may be wrong.

What I have done is :

1. Sort the data on column A and column F , so that all data pertaining to a user is together , and in the same order for every user. This is because the values in column F were in different order for different users ; we would then have one group of 101 , 108 , while another user would have 108 , 101. Identifying that these were the same was eliminated if we sorted the data first.

2. to concatenate all values in column F for each user in column H.

3. Next , I did a copy , Paste Special , Values in column H so that the data could be sorted on this column.

4. Next , the result was filtered to hide blanks.

You can see that for every group , all users having that group have been put together.

See if this can be replicated in your working file.

Narayan

Thanks this is really helping - could you please post your CONCATENATE formula string from step 2

I'm using =IF(A2<>A1,F2,H1 & "," & F2) in col H
Then =IF(A2<>A3,CONCATENATE("",H2,""),"") in Col I to get a summary for each user only on one line.

That seems right to me - thanks.

 

[NARAYANK991]

Hi ,

See the attached file , where the formulae in both column H and column I has been retained.

Narayan

 

[Giles Dickenson]

Thanks Narayan. This was a different way of looking at my issue than I was thinking but it's got me to a solution! Really appreciate your help.