Need to put dates of payments next to the party

Party 1 has paid on
01/01/2016
02/01/2016


Party 2 has paid on
03/01/2016
03/15/2016


This is what I have in a pivot table, but I want to move those dates to be in separate columns to the right of the Party. The pivot table places EVERY date in the top row, not in the same row as Party 1.

Hello
Try setting the Pivot to 'Show in Tabular Form' > Report Layout in the Design tab. Put the Party and Date in the Row Labels and the Dollar amounts in the Values.

DBY

Thanks, but that makes it do just as my example above. I am needing it to be more like


Party 1 01/01/2016 ; 02/01/2016
Party 2 03/01/2016 ; 03/15/2016

Sorry I thought you wanted Party in first column, dates in second and amount in the third. I don't know of a way to do what you show within a pivot table. You can put the dates across as column labels but other than that I not sure you can show it like that in a pivot table in Excel 2010. I hope I'm wrong and someone has a solution.

Yeah, i don't care how I can do it. Formula/macro, however. I just don't want the dates stacked underneath the Party. They need to go in a horizontal format. Thanks!

An array formula would do that, for example, if your data is in A1:B7:

Formula:

Please Login or Register  to view this content.

'Party 1', 'Party 2' etc. would be in E2 down. Not sure if you need the dollar amounts anywhere.

That's a potential lot of array formulas maybe 9000, it might slow the workbook down. I could perhaps try to work out a VBA solution and get back to you. If in the meantime someone comes in with a solution all's well.

ok, thank you.

Yeah I am lost.

Hi
Here attached is an example using VBA. It assumes all the REPS are sorted together as in your example. You would also need to create a list of them as I've done. I know you have hundreds but there are many ways in Excel to extract a list of them.

Wow, that looks great. I have no idea how to modify it though. If I copy and paste the news onto the list and the effective dates, only the G2,G3 fills out.

So I figured you'd just need to change the vba to G2:GX and boom, but I get an error.

I came up with a index method too, do you know anything about those?

Please Login or Register  to view this content.

works great, except you have to keep the duplicates on the sheet so Rep 1 is listed five times.

Yes the code would need to be adapted for your actual worksheet but the principle should be okay if the layout is consistent with your sample.

So I figured you'd just need to change the vba to G2:GX and boom, but I get an error.

Not sure what G2:GX is as a range reference. Is it a typing error? My code also used a Named range (Payment_Customer_Name) so that would have to made too.

I was going to suggest using Index as a formula solution:

Formula:

Please Login or Register  to view this content.

This works with your sample data but it is an Array formula, it has to be entered with Ctrl+Shift+Enter. If done correctly you'll see curly brackets {} around the formula.

DBY