Resorting / Reformatting data in columns to different layout

It doesn't solve your last request, but as a very visual solution I've attached a potential solution.

This uses a pivot table combined with conditional formatting to display each customer with the "Sorts" they have achieved. If you click on the pivot table you will see which fields I've used for the rows and columns. I made the format tabular (see Report Layout button under Pivot Table Tools/Design). The main pivot table gives a count of each occurrence of sort achieved for each customer. I then used conditional formatting with a formula to say that if the cell value is >0, make the font and fill colour the same. The numbers are still there, but just not visible, so you could still use them in other formulas or calculations if you wanted.

Hope that helps

This is awesome. THank you!

I tried setting up the pivot table to the actual workbook it will be used in.... How do I change the data source for a pivot table??? (note I have NEVER used a pivot table in my life)

Thanks! I got it working (so it seems).

Now to get the Pivot table refresh into the Macro that generates that list it and it will be perfect.

Thanks again!

Put this in an appropriate place in your code (presumably somewhere after it generates the data list):

Please Login or Register  to view this content.

That will refresh all the pivot tables in the workbook... in case you feel like adding some more now you've discovered them

Thanks!!! I really appreciate your help.

Final question, and I'll leave you alone for a while)

Is there a way to rearrange the order the columns appear in the pivot table? It seems that it just goes down column A, and the order in which data appears in Column A, is the order the header row displays the info. So for example, one of the column headings is "Activation".... well in the export data, that appears 4th.... out of 5. But I would like it to appear 5th in the header row..... does that make sense??

Update: I set the macro that pulls the raw data so that it would sort the data before it goes into the pivot table... and the columns are still not appearing in the correct order.....

I see them as columns 1, 2, 4, 5, 3

Is there a way to manually force the column order???

Wasn't ignoring you... just went to get food. It's a bit later here in England

You can change the columns in a pivot table to be in whichever order you want. Click on the column you want to move then hover the cursor until you get the black cross (the one with the arrows on the points - sure it must have a technical name but don't know what it is!). You can then click and drag the column to another position... it shows as a narrow bar so you can see where it's going.

If you've ever used Excel tables, it's the same way as you drag table columns about.

Give it a try and let me know how you get on. Shout out if you need more help

YOU. ARE. AWESOME.

Thank you.

Thank you for that very positive feedback It was a pleasure to help



Excel is a constant learning process and it's great to help each other. If I've helped you today, a click on the star on the left is appreciated.