Simplest way to compare tabular data for two dates (preferably using PIVOT)

x0nar

New Member
Joined
May 10, 2016
Messages
34
Hi. I'm looking for the simplest way to analyze data stored in a tabular format in an excel sheet and make comparisons (text as well as value fields) using dates as columns.

To clarify, I have data for five days spanning over 20 different columns stored in an excel sheet. For each day, the date is stored in the date column (a part of 20 columns).
Consider something like this

Raw Data


Desired output.

DateIDOwnerAmount
01-05-2021​
A01ABC2,000
01-05-2021​
B01DEF10,000
01-05-2021​
C01EFG6,000
02-05-2021​
A01ABC3,000
02-05-2021​
C01CDE5,000
03-05-2021​
A01XYZ7,000
03-05-2021​
C01CXZ8,000
03-05-2021​
D01QRT4,000
03-05-2021​
E01TSU6,000


Desired format
01-05-2021​
02-05-2021​
03-05-2021​
IDOwnerAmountOwnerAmountOwnerAmount
A01ABC2,000ABC3,000XYZ7,000
B01DEF10,000
C01EFG6,000CDE5,000CXZ8,000
D01QRT4,000
E01TSU6,000

(Assume that the date columns are merged for owner and amount fields)

In the table above, as you can see that the data is automatically arranged in a columnar format based on the available dates. And all of the data is referenced against the ID field to create a comparison table.
Also, the above

Current efforts:
  1. Using PIVOT Table, I can partially generate this format by Putting ID as Rows, Dates as Columns, and Amounts as values. However, when it comes to text fields pivot fails to perform and returns count of values instead of the actual tax. I am aware of using Data Model and creating a DAX-based Calc field (CONCATENATEX) to return the text values. But, as I said above source data can have more than 20 columns, which could be a big pain to create dax fields for every column. Becomes more time-consuming.
  2. An alternative is to create a set of unique values extracted from ID, and then using VLOOKUP replicate all of the fields for all the different dates as columns. e.g. Owner_01-05-2021, Owner_02-05-2021,Owner_03-05-2021. However, this is also very time-consuming, and needless to mention this will 100 columns for 5 dates, which honestly becomes unnecessarily vast.
  3. In the given example we have only considered ID as a lookup field, however, there could be multiple lookup fields (or Pivot Row items), that would be required. This does not seem to be manageable using method 2.

The reason I'm persistent in using PIVOT table is that it helps me select the columns or rearrange the rows, or even add filters very quickly.
If anyone could point out the right direction, I would be immensely grateful to them.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
you only want it pivot? or formulae will do?
 
Last edited:
Upvote 0
is this what you are looking at if given?

Book1
ABCDEF
1Raw Data
2DateIDOwnerAmountHelper
31-May-21A01ABC2,0001
41-May-21B01DEF10,0002
51-May-21C01EFG6,0003
62-May-21A01ABC3,0003
73-May-21C01CDE5,0004
83-May-21A01XYZ7,0005
93-May-21C01CXZ8,0006
103-May-21D01QRT4,0007
113-May-21E01TSU6,0008
1251,000
13Desired Output
14
15SrIDOwner1-May-212-May-213-May-21
161A01ABC20003000 
172B01DEF10000  
183A01EFG6000  
194C01CDE  5000
205A01XYZ  7000
216C01CXZ  8000
227D01QRT  4000
238E01TSU  6000
2418000300030000
Sheet1
Cell Formulas
RangeFormula
E3:E11E3=SUM(E2,SUMPRODUCT(--($B$3:B3&"|"&$C$3:C3=B3&"|"&C3))=1)
D12D12=SUM(D3:D11)
B16B16=INDEX(B$3:B$11,MATCH($A16,$E$3:$E$11))
C16:C23C16=INDEX(C$3:C$11,MATCH($A16,$E$3:$E$11,0))
D16:F23D16=IFERROR(INDEX($D$3:$D$11,MATCH(1,INDEX(($C$3:$C$11=$C16)*($A$3:$A$11=D$15),0),0)),"")
D24:F24D24=SUM(D16:D23)
A17:A23A17=A16+1
B17:B23B17=INDEX($B$3:$B$11,MATCH($A17,$E$3:$E$11))
 
Upvote 0
Method 2 , if you do not want to use helper column

Unique Extraction.xlsx
ABCDEFGHIJKL
1Raw DataUnique Count8
2DateIDOwnerAmountHelper
31-May-21A01ABC2,0001IDOwner1-May-212-May-213-May-21
41-May-21B01DEF10,0002A01ABC20003000 
51-May-21C01EFG6,0003C01CDE  5000
62-May-21A01ABC3,0003C01CXZ  8000
73-May-21C01CDE5,0004B01DEF10000  
83-May-21A01XYZ7,0005C01EFG6000  
93-May-21C01CXZ8,0006D01QRT  4000
103-May-21D01QRT4,0007E01TSU  6000
113-May-21E01TSU6,0008A01XYZ  7000
1251,00018000300030000
Sheet1
Cell Formulas
RangeFormula
L1L1=SUM(IF(FREQUENCY(IF($C$3:$C$11<>"",MATCH("~"&$C$3:$C$11,$C$3:$C$11&"",0)),ROW($C$3:$C$11)-ROW($C$3)+1),1))
H4:H11H4=INDEX($B$3:$B$11,MATCH($I4,$C$3:$C$11,0))
I4:I11I4=IF(ROWS(I$4:I4)>$L$1," ",INDEX($C$3:$C$11,MATCH(0,COUNTIF($C$3:$C$11,"<"&$C$3:$C$11)-SUM(COUNTIF($C$3:$C$11,$I$3:I3)),0)))
J4:L11J4=IFERROR(INDEX($D$3:$D$11,MATCH(1,INDEX(($C$3:$C$11=$I4)*($A$3:$A$11=J$3),0),0)),"")
J12:L12J12=SUM(J4:J11)
E3:E11E3=SUM(E2,SUMPRODUCT(--($B$3:B3&"|"&$C$3:C3=B3&"|"&C3))=1)
D12D12=SUM(D3:D11)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

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