Transposing/Summarising data based on one column

nikio8

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
Hi all,
Basically I have two types of stored data, that needs to be reconciled against each other based on media type.
The issue is that one has media is all in one column, not as headings.
I would like it to be as it is in the columns F:K, which is a pivot table, as well as what server data looks like.

My main point is to use data and not formulas, especially thousands of sumifs. This is where I am at, just need to get the two data in one sheet, but am not able to resolve this issue.
I was thinking to merge the data, say "C1 & ~ & A1 & ~ & B1 & ~ D1", sort and then split the data, where amount would go under Media column.

Unfortunately, not easy as there is quite a bit of data, and media type can vary, sometime there can only be cash, sometimes eftpos, could be something new, short period has some, long period has all types etc...

Hope I am making sense and there is an easy way to do this.
The key issue is, the more complicated this step is, the more complicated recon will be and I would like to create new sheet for each store.



ABCDEFGHIJKL
DateStoreMediaAmountSum of AmountMedia
Store 1CashDateStoreCashEftVoucherGrand Total
Store 2EftStore 1
Store 3VoucherStore 2
Store 4EftStore 3
Store 5CashStore 4
Store 6EftStore 5
Store 7CashStore 6
Store 1EftStore 7
Store 2CashStore 1
Store 3EftStore 2
Store 4CashStore 3
Store 5EftStore 4
Store 6CashStore 5
Store 7EftStore 6
Store 6CashStore 7
Store 6
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/01/2018[/TD]

[TD="align: right"] $ 5.08 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/01/2018[/TD]

[TD="align: right"] $ 5.01 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/01/2018[/TD]

[TD="align: right"] $ 2.30 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2.30 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/01/2018[/TD]

[TD="align: right"] $ 0.59 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 7.20 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 7.20 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4/01/2018[/TD]

[TD="align: right"] $ 0.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 9.81 [/TD]
[TD="align: right"] $ 9.81 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/01/2018[/TD]

[TD="align: right"] $ 4.31 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]4/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 9.34 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 9.34 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/01/2018[/TD]

[TD="align: right"] $ 3.33 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/01/2018[/TD]

[TD="align: right"] $ 8.24 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 8.24 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7/01/2018[/TD]

[TD="align: right"] $ 6.72 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 2.93 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2.93 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/01/2018[/TD]

[TD="align: right"] $ 0.65 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/01/2018[/TD]

[TD="align: right"] $ 0.19 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 0.19 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9/01/2018[/TD]

[TD="align: right"] $ 0.99 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 4.97 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 4.97 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10/01/2018[/TD]

[TD="align: right"] $ 1.85 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/01/2018[/TD]

[TD="align: right"] $ 4.33 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 4.33 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11/01/2018[/TD]

[TD="align: right"] $ 2.25 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 7.33 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 7.33 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/01/2018[/TD]

[TD="align: right"] $ 9.46 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/01/2018[/TD]

[TD="align: right"] $ 6.47 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 6.47 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13/01/2018[/TD]

[TD="align: right"] $ 1.69 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 4.98 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 4.98 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14/01/2018[/TD]

[TD="align: right"] $ 4.73 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]13/01/2018[/TD]

[TD="align: right"] $ 2.20 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2.20 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15/01/2018[/TD]

[TD="align: right"] $ 7.58 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]14/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 6.59 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 6.59 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15/01/2018[/TD]

[TD="align: right"] $ 1.82 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 1.82 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 25.56 [/TD]
[TD="align: right"] $ 43.35 [/TD]
[TD="align: right"] $ 9.81 [/TD]
[TD="align: right"] $ 78.72 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi Sandy. Whatever i do, i would like to replicate on over 100 sheets. There is just that much data, it needs to broken down. Not sure i can use power query on many sheets. But that is how i got the data so far.
 
Upvote 0
Hard to understand what you want to achieve.
If all tables on 100 sheets are in the same format and you want to summarize them, just append all into one then pivot Media by Amount.
Describe your problem not your own solution.
 
Upvote 0
Hi, just trying to do as in the first post. Get Media column to become heading column, same as pivot table shown.
Pivot table does it easy, I was hoping there is an easy way to do it.
 
Upvote 0
Here is PQ result for your source table

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Store[/td][td=bgcolor:#5B9BD5]Media[/td][td=bgcolor:#5B9BD5]Amount[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Store[/td][td=bgcolor:#70AD47]Cash[/td][td=bgcolor:#70AD47]Eft[/td][td=bgcolor:#70AD47]Voucher[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
01/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 1[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
5.08​
[/td][td][/td][td=bgcolor:#E2EFDA]
01/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 1[/td][td=bgcolor:#E2EFDA]
5.08​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
02/01/2018​
[/td][td]Store 2[/td][td]Eft[/td][td]
5.01​
[/td][td][/td][td]
02/01/2018​
[/td][td]Store 2[/td][td][/td][td]
5.01​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
03/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 3[/td][td=bgcolor:#DDEBF7]Voucher[/td][td=bgcolor:#DDEBF7]
0.59​
[/td][td][/td][td=bgcolor:#E2EFDA]
03/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 3[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
0.59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04/01/2018​
[/td][td]Store 4[/td][td]Eft[/td][td]
0​
[/td][td][/td][td]
04/01/2018​
[/td][td]Store 4[/td][td][/td][td]
0​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 5[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
4.31​
[/td][td][/td][td=bgcolor:#E2EFDA]
05/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 5[/td][td=bgcolor:#E2EFDA]
4.31​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
06/01/2018​
[/td][td]Store 6[/td][td]Eft[/td][td]
3.33​
[/td][td][/td][td]
06/01/2018​
[/td][td]Store 6[/td][td][/td][td]
3.33​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
07/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 7[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
6.72​
[/td][td][/td][td=bgcolor:#E2EFDA]
07/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 7[/td][td=bgcolor:#E2EFDA]
6.72​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
08/01/2018​
[/td][td]Store 1[/td][td]Eft[/td][td]
0.65​
[/td][td][/td][td]
08/01/2018​
[/td][td]Store 1[/td][td][/td][td]
0.65​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
09/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 2[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
0.99​
[/td][td][/td][td=bgcolor:#E2EFDA]
09/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 2[/td][td=bgcolor:#E2EFDA]
0.99​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
10/01/2018​
[/td][td]Store 3[/td][td]Eft[/td][td]
1.85​
[/td][td][/td][td]
10/01/2018​
[/td][td]Store 3[/td][td][/td][td]
1.85​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
11/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 4[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
2.25​
[/td][td][/td][td=bgcolor:#E2EFDA]
11/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 4[/td][td=bgcolor:#E2EFDA]
2.25​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12/01/2018​
[/td][td]Store 5[/td][td]Eft[/td][td]
9.46​
[/td][td][/td][td]
12/01/2018​
[/td][td]Store 5[/td][td][/td][td]
9.46​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
13/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 6[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
1.69​
[/td][td][/td][td=bgcolor:#E2EFDA]
13/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 6[/td][td=bgcolor:#E2EFDA]
1.69​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
14/01/2018​
[/td][td]Store 7[/td][td]Eft[/td][td]
4.73​
[/td][td][/td][td]
14/01/2018​
[/td][td]Store 7[/td][td][/td][td]
4.73​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15/01/2018​
[/td][td=bgcolor:#DDEBF7]Store 6[/td][td=bgcolor:#DDEBF7]Cash[/td][td=bgcolor:#DDEBF7]
7.58​
[/td][td][/td][td=bgcolor:#E2EFDA]
15/01/2018​
[/td][td=bgcolor:#E2EFDA]Store 6[/td][td=bgcolor:#E2EFDA]
7.58​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
if you want do the same for all your sheets ==>> (see post#4)
then you can do your next action ;)
 
Last edited:
Upvote 0
Hi
The data has to be split to many sheets, not appended.
However you are probably right, I can try using pq, split later. I've used pq to get the 4 columns, can just keep going.
Can you share pq code?

Most likely every store will have an issue and managers will want to see data summary. Say credit payments will have 3 day delay, etc etc...
Here is a very simple way, below.

I will try to understand this one
https://www.howtoexcel.org/vba/how-...te-workbooks-based-on-the-values-in-a-column/


Code: 
Sub PivotMe()
'
    Dim i, j As Integer
    
    Range("C1").Copy Destination:=Range("I1")

'get unique media --- better would be to just use headings for all media types
    Range("C1:C16").[B]AdvancedFilter[/B] Action:=xlFilterCopy, CopyToRange:=Range( _
        "I1"), [B]Unique:=True[/B]
    
    Range("A1:B16").Copy Destination:=Range("K1")
   
    Range("I2:I4").Copy
    Range("M1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, [B]Transpose:=True[/B]
        
    For i = 2 To 16
        Select Case Cells(i, "C")
        Case "Cash"
            Cells(i, "M").Value = Cells(i, "D").Value
        Case "Eft"
            Cells(i, "N").Value = Cells(i, "D").Value
        Case "Voucher"
            Cells(i, "O").Value = Cells(i, "D").Value
        Case Else
            Cells(1, "P").Value = "New"
            Cells(i, "P").Value = Cells(i, "D").Value
        End Select
    Next i
        
End Sub
 
Upvote 0
nikio8 said:
Can you share pq code?
Click to expand...
sure
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Store", type text}, {"Media", type text}, {"Amount", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Media]), "Media", "Amount", List.Sum),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}})
in
    #"Changed Type1"[/SIZE]
I can't help you with VBA
 
Upvote 0
Thanks, very smart.
Transform, Pivot.
I knew it is something simple.

posibly I can merge data, and then used advanced filter to split into sheets.
fun times ahead :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
184
Alaa mg
A
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
322
Alaa mg
A
Deepakchoudhary
  • Question Question
Database & Bar Chart preparation for comparison of 3 parameters, 5 items on Dynamic selection of OUTLETS
Replies
0
Views
100
Deepakchoudhary
Deepakchoudhary
A
  • Solved
Create Named Range for each column based on column heading text.
Replies
5
Views
302
Cubist
Cubist
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
852
paulkc
P

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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