Data Table to Pivot Table then Update Data from Pivot Table

[jbesclapez]

Hello,

I would like to have a Data Table that creates a Pivot Table. Then I want to insert or change data in the pivot table that would "update" the Data Table back. It will be a 2 steps thing.

Here is the Table :

ColA	ColB	ColC	ColD
CH1.4	PE	Q001R19	OK
LOU	PE	Q001R13	ToDo
CH1.4	DIAR	Q001R22	OK
CH1.4	DIAR	Q001R31	OK
BID	LOCO	Q001R21	ToDo
BID	LOCO	Q001R31b	ToDo
CH1.4	LOCO	Q005R03	ToDo
GAP	OE	Q005R01	INCOH
CH1.4	OE	Q005R04	INCOH
CH1.4	OE	Q006R05	OK
GAP	OE	Q006R01	OK
BID	PE	Q001R19	OK
GAP	PE	Q001R13	ToDo
CH1.4	PE	Q005R04	OK
LOU	PE	Q001R31b	ToDo

<tbody>
</tbody>

And it creates a table like below (same then a pivot table)
Please note the cell A1. It has to be filtered on that which is the ColB of the table.

PE
	Q001R19	Q001R13	Q001R22	Q001R31	Q001R21	Q001R31b	Q005R03	Q005R01	Q005R04	Q006R05	Q006R01
CH1.4	OK								OK
LOU		ToDo				ToDo
BID	OK
GAP		ToDo

<tbody>
</tbody>

I know it is tricky because a Pivot table cannot be done as it does not work with Data. (only count, sum...). The trick I used was to put a unique number in ColD and in the pivot table show the MAX number. Then I did a replace to change the number with actual Name in ColD. However, I have not idea how to go back from Pivot Table to Data Table...

I hope one of you will know how to do that better than me.

Thanks for your time

 

[Fazza]

yes, that is the SQL
for use in this instance in a query table

exact set up might be version dependent

if the file is saved with defined name 'YourTable' for the source data table/range,
ALT-D-D-N to start the wizard, Excel files, OK, select the file, then the data table (YourTable), then any field and continue to the last step.
At this point select the option to edit in MS Query, then via the 'SQL' button see the current SQL, fully replace it by the above SQL, then OK.
OK to the message about not being able to graphically represent.
see the result set,
via the 'open door' icon exit MS Query & create the query table in a worksheet

it is refreshable like a pivot table.

cheers

 

[krrishkrsna]

works perfectly and faster than formula

 

[Fazza]

there are old threads where query table results took less than a second and (large numbers of) array formulas took hours to get the same answer

a little SQL can do a lot of work

 

[krrishkrsna]

hi Fazza,

can you tell the method to view the queries after it was written, plz

 

[Fazza]

there are various ways, and how to see the SQL will be version dependent

in older Excel versions you could go through the wizard again. or in the VBE's immediate window debug.print the SQL

i'm not so familiar with newer versions & even though I use a recent version still use the old keyboard shortcuts, not the ribbon. try looking for data properties, or check in Excel help. (VBA would also be an option, btw, but is not necessary)

 

[jbesclapez]

there are various ways, and how to see the SQL will be version dependent

in older Excel versions you could go through the wizard again. or in the VBE's immediate window debug.print the SQL

i'm not so familiar with newer versions & even though I use a recent version still use the old keyboard shortcuts, not the ribbon. try looking for data properties, or check in Excel help. (VBA would also be an option, btw, but is not necessary)

Hi Fazza, and thanks for your info.
However, I am not familiar with that way of doing SQL in the excel.
Do you mind creating a small exemple that we could download and look? I am really curious on how to do that...

 

[Fazza]

hi. To get started, please follow the instructions in post #11 .

I just went to YouTube and queried using "excel query table" and found some relevant info.

Post #21 , and other posts, in this old thread give some links/hints/information, https://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

regards