GETPIVOTDATA vs. INDEX/MATCH

**esbencito** · 03-09-2018, 02:40 AM

Hi all,

What is the best and most efficient way to retrieve attributes for every corresponding item from the data tab which is refreshed daily. Put everything in a pivot first and then INDEX/MATCH it from there? or get it directly from the raw data tab?

The information is constantly changing, columns are added and removed, attribute names change here and there... so I guess pivoting everything first would give some more stability? Is there a way to use GETPIVOTDATA to get the attributes for each item number? Or can it only be used for the metric columns?

Also, if items are added daily in the data sheet, is there a way to automatically find them and add them to the bottom of the list in the Range sheet table?

**FDibbins** · 03-09-2018, 02:54 AM

I have not looked at your file (yet), but depending on the amount of data you are working with, probably INDEX/MATCH into your raw data, maybe the quicker method - PT's need to be refreshed to include any new data

**esbencito** · 03-09-2018, 03:30 AM

The problem is, as mentioned, the data changes, columns are added or removed, so having everything in a pivot would simplify it in my opinion, especially since the data serves several files, meaning that you would only have to refresh the pivot rather than copy/pasting the data into all of them. Is there a way to use GETPIVOTDATA for the attributes? Or would I still have to use INDEX/MATCH even when using a pivot>?

**xlnitwit** · 03-09-2018, 03:42 AM

GETPIVOTDATA will only work for the metrics.

**esbencito** · 03-09-2018, 04:23 AM

hmm.. ok! so you would also suggest to INDEX/MATCH the data from the pivot in the way I have done it in my sample file?

**xlnitwit** · 03-09-2018, 05:08 AM

Not really. Since you have Office 2016 I'd suggest you use Get and Transform rather than a pivot table.

**esbencito** · 03-09-2018, 05:11 AM

Get and Transform? How would that look like

**xlnitwit** · 03-09-2018, 05:14 AM

You basically query the data- the options are on the Data tab. (I am currently using a Mac which doesn't support that)

I have just noticed your original comment of "The information is constantly changing, columns are added and removed, attribute names change here and there". Does that mean that the column headers in the raw data change?

**esbencito** · 03-09-2018, 05:39 AM

I could make sure that the existing names stay the same, but there will be new columns with new headers added in between existing ones...

That's why I thought a pivot would be best as it doesn't show them if not manually added to the pivot field

**xlnitwit** · 03-09-2018, 05:43 AM

In that case all you need to do is turn the source data into a table, load it into Get and Transform and then group the fields you want and summarise the values you want. You can then output the result as a table on your destination sheet. When the source data changes, you simply refresh the query and everything will be up to date.

PS I will have access to Excel 2016 later so I can mock up an example for you then if necessary.

**esbencito** · 03-09-2018, 05:54 AM

hmm.. will need to take some time to explore that on Monday. Never actually heard of that before as I just recently switched to Office 2016. In case you happen to be on a Windows PC with the newest Excel at some point in the future, an example file with my data above would be highly appreciated

**esbencito** · 03-09-2018, 05:56 AM

Originally Posted by xlnitwit

PS I will have access to Excel 2016 later so I can mock up an example for you then if necessary.

haha, sorry, just saw your edited post! Thanks, would love to see how that would look like!

**xlnitwit** · 03-09-2018, 12:52 PM

Is the resulting list supposed to have every item in it, or only specific ones?

**esbencito** · 03-09-2018, 09:33 PM

only specific ones!

**esbencito** · 03-12-2018, 02:52 AM

Did you already have time to mock up an example of GET and TRANSFORM ?

**xlnitwit** · 03-12-2018, 04:53 AM

I did not I'm afraid. If you are still stuck, I will endeavour to do so tomorrow.

**esbencito** · 03-13-2018, 05:55 AM

Originally Posted by xlnitwit

I did not I'm afraid. If you are still stuck, I will endeavour to do so tomorrow.

Still stuck, since I have never done a GET and TRANSFORM

**xlnitwit** · 03-13-2018, 06:05 AM

Do you want the styles duplicated in the result table for some reason, as you have now?

**esbencito** · 03-13-2018, 06:08 AM

Unfortunately, 90% of styles have two or even three different delivery dates (as you can see in the pivot), hence they need to be duplicated...

**xlnitwit** · 03-13-2018, 06:10 AM

I noticed that but your result table does not include the delivery date, so what do you expect in the duplicated row? Just the same data again?

**esbencito** · 03-13-2018, 06:14 AM

Sorry, maybe here's the confusion. I just mocked up a sample file and forgot to add the attribute "Seasonal Delivery", it should be included in the table!! The other data should just be repeated!

**xlnitwit** · 03-13-2018, 06:58 AM

Here is a simple example. You can edit the Merge query through the queries and connections pane to adjust what columns are output. It does look to me as though you could also achieve the end result using an advanced filter.

**esbencito** · 03-14-2018, 02:26 AM

awesome! Thank you! Need to play with it for a while to see what it can do

GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Re: GETPIVOTDATA vs. INDEX/MATCH

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?

[SOLVED] Index / Match - match 3 input values and return the results from the index

[SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions