Transferring data from Columns to rows, not using a Pivot table

[russelldt]

I am reposting this, as I have not had a workable response - probably due to me not describing the problem eloquently.

I have a excel file with 2 sheets.

Sheet 1 has "Job Number" in Column C, and empty cells for Invoice numbers, in columns M, N, O, P, Q and R

Sheet 2 has a dump from an accounting package, and the data is organized in columns. Column A has Job Number, Column B has the invoice number and Column C has the Paid flag. There are many invoices per job.


I need the invoice numbers and Paid status from Sheet 2 to go into Sheet 1against the correct job number. The first invoice number for a Job, to go in column M, the second on N, and so on. Once the invoice is flagged as paid, the invoice number must show a P after (e.g 1956, and once paid 1956P).

Any of the geniuses out there able to help?

 

[JGordon11]

If your version has Power Query (Get and Transform) then this is another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.Sort(Source,{{"Invoice", Order.Ascending}}),
    tbl2 = Table.FromRecords(Table.TransformRows(tbl1, each if [Paid] then Record.TransformFields(_, {{"Invoice", (f)=> Text.From(f) & "P"}}) else _)),
    tbl3 = Table.Group(tbl2, {"Job"}, {{"All", each _[Invoice]}}),
    tbl4 = Table.Sort(tbl3,{{"Job", Order.Ascending}}),
    cols = List.Max(List.Transform(tbl4[All], each List.Count(_))),
    tbl5 = Table.TransformColumns(tbl4, {{"All", each _ & List.Repeat({null}, cols - List.Count(_))}}),
    InvoiceHeaders = List.Accumulate({1..cols}, {}, (s,c)=> s & {"Invoice." & Text.From(c)}),
    Result = Table.FromRows(List.Transform(List.Zip({tbl5[Job], tbl5[All]}), each {_{0}} & _{1}), {"Job"} & InvoiceHeaders)
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Job	Invoice	Paid		Job	Invoice.1	Invoice.2	Invoice.3	Invoice.4	Invoice.5
2	8520	57749	TRUE		1230	40623P	46427	64049	75582
3	1230	40623	TRUE		3362	18079
4	6717	77302	FALSE		3667	38665P
5	8520	74448	FALSE		6717	75497P	77302
6	1230	64049	FALSE		8416	55639
7	8520	94210	FALSE		8520	11111	22062P	57749P	74448	94210
8	8416	55639	FALSE		8709	60800P
9	8709	60800	TRUE
10	8520	22062	TRUE
11	1230	46427	FALSE
12	3362	18079	FALSE
13	3667	38665	TRUE
14	1230	75582	FALSE
15	6717	75497	TRUE
16	8520	11111	FALSE
17
Sheet1 (2)