Creating Column based on another cell

[chelseariver90]

Hi,

I have a table of invoice details for materials costed to Jobs. the table has Job Number, Memo, invoice number & Total columns. The memo column holds the name of the supplier. There could be 300 entries with the one job number but with different suppliers, invoice numbers and totals. I need to put in a formula that will Pick up on the Supplier name, create a column with a header and then put the total into that column for that job number. I tried doing this with a formula and then a summary (grouping by Job Number, Category with Sum Total but it shows 2 Job numbers one with the total for all the materials that dont have the suppliers name and then one that has the total of only those with the suppliers name outlined in the Formula tool. Example below will hopefully explain it better.

Job Number	Memo	Invoice number	Total
A123	Painters	1231	50
A123	Wood	1232	60
A123	Wood	1233	80

For the above i would like 2 columns created that would then show as below for the job number summary. The Invoice number and Memo are not really needed any more.

Job Number	Painters	Wood
A123	50	140

Thanks

[AndrewS · Accepted solution]

Hi @chelseariver90 

A cross tab should get you that result. Workflow attached for reference.

[fmvizcaino · Accepted solution]

Hi @chelseariver90 ,

You can use the cross-tab tool to achieve what you desire.

I'm attaching an example to show you how.

LEt me know if that works for you.

Best,

Fernando Vizcaino