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?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[SanjayGMusafir]

With Excel 365 in your hand that is very much possible with XLOOKUP if I'm not wrong.

If you can figure out, that's good. Else share a sample data using XL2BB to help you out

 

[JGordon11]

an example:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Job	Invoice	Paid		Job	Invoices
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	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
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=SORT(UNIQUE(A2:A15))
F2:I2,F7:I7,F5:G5,F3:F4,F6,F8	F2	=LET(a,SORT(FILTER($A$2:$C$15,$A$2:$A$15=E2),2), i, INDEX(a,,2),p, INDEX(a,,3),TRANSPOSE( IF(p, i&"P", i)))
Dynamic array formulas.

 

[russelldt]

an example:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Job	Invoice	Paid		Job	Invoices
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	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
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=SORT(UNIQUE(A2:A15))
F2:I2,F7:I7,F5:G5,F3:F4,F6,F8	F2	=LET(a,SORT(FILTER($A$2:$C$15,$A$2:$A$15=E2),2), i, INDEX(a,,2),p, INDEX(a,,3),TRANSPOSE( IF(p, i&"P", i)))
Dynamic array formulas.

an example:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Job	Invoice	Paid		Job	Invoices
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	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
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=SORT(UNIQUE(A2:A15))
F2:I2,F7:I7,F5:G5,F3:F4,F6,F8	F2	=LET(a,SORT(FILTER($A$2:$C$15,$A$2:$A$15=E2),2), i, INDEX(a,,2),p, INDEX(a,,3),TRANSPOSE( IF(p, i&"P", i)))
Dynamic array formulas.

an example:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Job	Invoice	Paid		Job	Invoices
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	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
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=SORT(UNIQUE(A2:A15))
F2:I2,F7:I7,F5:G5,F3:F4,F6,F8	F2	=LET(a,SORT(FILTER($A$2:$C$15,$A$2:$A$15=E2),2), i, INDEX(a,,2),p, INDEX(a,,3),TRANSPOSE( IF(p, i&"P", i)))
Dynamic array formulas.

Thanks John exactly what I am looking for, but my version of Excel does not include Dynamic Array.

Is there another solution you could suggest?

 

[russelldt]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

With Excel 365 in your hand that is very much possible with XLOOKUP if I'm not wrong.

If you can figure out, that's good. Else share a sample data using XL2BB to help you out

Hello Sanjay, unfortunately I don't have the latest version that used Dynamic Array

I have the XL2BB add-on loaded, and sample data added. What do I need to do next.

Thank you

 

[mumps]

I see the sample data from JGordon but I don't see the data you posted.

 

[russelldt]

I see the sample data from JGordon but I don't see the data you posted.

 

[mumps]

What does Sheet1 look like?

 

[russelldt]

What does Sheet1 look like?

I was thinking that the solution could be added to this sheet, and I will link the cells in sheet 1 to the sheet. The layout proposed by JGorden is perfect, as I can then add the links to sheet 1 from there. So in effect, sheet I does not need to be part of the solution.

I dont Have dynamic ranges, so can’t use the sort and other functions