Creating a list of transactions (amount and date)

[nunez100]

Hi,

I was hoping to get help with a problem that I am unable to fix.

I have a spreadsheet with a list of customer purchases (name, purchase amount, purchase date). I wanted to find the best way to set up a formula to separate each persons purchases onto separate tabs, (each showing the amounts and dates), so that I could print out each tab as an itemised monthly statement for each customer.

I have thought about using the VLOOKUP function, but I have read that this can be inefficient if the table is large, so that it might slow excel down. I am pretty sure I need to do it with an INDEX function, perhaps INDEX and MATCH, or IFERROR and INDEX, but I am not sure what the best approach is to give a list of every relevant transaction for each customer, and to give both the amount and the date.

I have attached an example with the source data in the first tab, and the desired output data that in the second tab that hopefully a formula can generate. Hopefully there is a way to do this.

Many thanks

George

[sandy666]

is that what you want?

done with PowerQuery aka Get&Transform

I did some but not all

[nunez100]

Hi Sandy666, thank you very much for this. It does seem like a good way of doing it, even though it is not what I was expecting (I had never heard of the PowerQuery). Thanks. I will have to study it a bit to figure out what you have done. One reason that I was hoping to use an array of some sort was that I would like to have the results tabs automatically update as I add new transactions to the input table all month long, without me having to 'refresh' each tab each day (which a brief read up on PowerQuery seems to imply I would need to do). I thought that I could have an array on each tab set up to read all 500 rows of the input table (to allow up to 500 transactions to be listed per month), but capture only transactions (amount and date) for the relevant customer. Does your approach of using a PowerQuery allow me to have 500 blank rows on the input table that will automatically feed into the customer tabs as I gradually add more transactions?

[sandy666]

1st. you can use a little vba code to refresh Query Tables automatically
2nd. blank rows are not a good idea. if you will use Excel table (like I did it) and add new data (see point 1) it will be reflected in appropriate table on appropriate tab
3rd. you can define how often you want refresh all tables (minnimum is 1 minute) but I suggest to use Refresh All manually especially with a big data and refreshing automatically after insert every new row will take time more than necessary. IMHO better is add all new data then refresh, But it's up to you

[nunez100]

Thanks very much. I will try this. Thank you very much for this solution.

[sandy666]

You are welcome

---

Read me Try to avoid joining to my Black List by doing this below If that takes care of your original question, & to say Thanks and for better Motivation, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. If you did it already - ignore it. Thank you.