Generating a list based on TRUE/FALSE table

[choks90233]

Good afternoon everyone. I have a project I am working on that is absolutely breaking my brain. I am probably making it far more difficult than it needs to be, and if anyone can help me simplify it, I am more than open to the suggestions. For context, I have a line of items that are produced in set quantities, and I need to track them in a certain order. The problem is that I sometimes need to remove items due to defects. This in turn effects the order in which they will be stacked for the next stage of processing.

This is what I what I am envisioning in the end with the table on the left being the source of the items being produced and the table on the right being the stacked items before the next stage of processing. The colored numbers correspond to layers that I will show a list of further down. Each cell on the table on the right will have the matching designation from the table on the left provided that cell is checked. If the cell is unchecked, that will mean that particular item was removed and the next good item will have taken its place on the stack. Thats where it gets difficult. The Items are stacked in rows from right to left, and bottom to top.

This is what I am trying to avoid.

This is what I am hoping to accomplish. Now, below is what I imagine is needed for a list to populate the table above. Maybe it's possible to populate the table directly without creating a list first but I cannot get my brain wrapped around the array formulas I need to make it work.

Please help!

 

[lrobbo314]

How about this?

Excel
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
41	1	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		G6	1	G5	8	F4	15	E3	22	E2	29	D1	36
42	2	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE		F6	2	E5	9	E4	16	D3	23	C2	30	C1	37
43	3	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		E6	3	D5	10	D4	17	C3	24	B2	31	B1	38
44	4	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE		D6	4	C5	11	C4	18	B3	25	A2	32	A1	39
45	5	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	TRUE		C6	5	B5	12	A4	19	A3	26	G1	33		40
46	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		B6	6	A5	13	G3	20	G2	27	F1	34		41
47		A	B	C	D	E	F	G		A6	7	G4	14	F3	21	F2	28	E1	35		42
48										Layer 1		Layer 2		Layer 3		Layer 4		Layer 5		Layer 6
Sheet3

Cell Formulas
Range	Formula
J41:J46	J41	=SEQUENCE(6)
S41:AD47	S41	=LET(an,TOCOL(IF(K41:Q46,K$47:Q$47&$J41#,"")),f,FILTER(an,an<>""),rv,INDEX(f,SEQUENCE(ROWS(f),,ROWS(f),-1)),a,TRANSPOSE(WRAPROWS(rv,7,"")),n,ROWS(J41#)*COLUMNS(K47:Q47),ns,TRANSPOSE(WRAPROWS(SEQUENCE(n),7)),h,HSTACK(a,ns),CHOOSECOLS(h,TOROW(TRANSPOSE(SEQUENCE(2,6)))))
Dynamic array formulas.

 

[choks90233]

that looks fantastic. except my company has yet to upgrade to the latest version of excel and SEQUENCE is not a formula I can utilize.

 

[lrobbo314]

It would be helpful in the future if you have the version you're on in your profile.

Seems like it would be a mess without the dynamic array formulas.

I'd have to play around and see if I could replicate it in PowerQuery. Or doing it with VBA wouldn't be a big lift. Is VBA and or PowerQuery available to you?

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[choks90233]

It would be helpful in the future if you have the version you're on in your profile.

Seems like it would be a mess without the dynamic array formulas.

I'd have to play around and see if I could replicate it in PowerQuery. Or doing it with VBA wouldn't be a big lift. Is VBA and or PowerQuery available to you?

I haven't used VBA much but it is available.

 

[lrobbo314]

Ok. The VBA below produced these results.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		G6	1	G5	8	F4	15	E3	22	E2	29	D1	36
2	2	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE		F6	2	E5	9	E4	16	D3	23	C2	30	C1	37
3	3	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		E6	3	D5	10	D4	17	C3	24	B2	31	B1	38
4	4	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE		D6	4	C5	11	C4	18	B3	25	A2	32	A1	39
5	5	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	TRUE		C6	5	B5	12	A4	19	A3	26	G1	33
6	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		B6	6	A5	13	G3	20	G2	27	F1	34
7		A	B	C	D	E	F	G		A6	7	G4	14	F3	21	F2	28	E1	35
Sheet1

Sub test()
Dim r As Range:         Set r = Range("B1:H" & Range("H" & Rows.Count).End(xlUp).Row - 1)
Dim AR() As Variant:    AR = r.Value2
Dim RES As Variant:     ReDim RES(1 To UBound(AR, 2), 1 To UBound(AR) * 2)
Dim IDX As Integer:     IDX = 1
Dim PTR As Integer:     PTR = 1
Dim CNT As Integer:     CNT = 1


For i = UBound(AR) To 1 Step -1
    For j = UBound(AR, 2) To 1 Step -1
        If IDX Mod 8 = 0 Then
            IDX = 1
            PTR = PTR + 2
        End If
        If AR(i, j) Then
            RES(IDX, PTR) = Chr(j + 64) & i
            RES(IDX, PTR + 1) = CNT
            IDX = IDX + 1
            CNT = CNT + 1
        End If
    Next j
Next i

Range("J1").Resize(UBound(RES), UBound(RES, 2)).Value = RES
End Sub

 

[lrobbo314]

Here is also a way using some helper columns and regular old formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		G6	1	G5	8	F4	15	E3	22	E2	29	D1	36
2	2	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE		F6	2	E5	9	E4	16	D3	23	C2	30	C1	37
3	3	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		E6	3	D5	10	D4	17	C3	24	B2	31	B1	38
4	4	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE		D6	4	C5	11	C4	18	B3	25	A2	32	A1	39
5	5	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	TRUE		C6	5	B5	12	A4	19	A3	26	G1	33		40
6	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		B6	6	A5	13	G3	20	G2	27	F1	34		41
7		A	B	C	D	E	F	G		A6	7	G4	14	F3	21	F2	28	E1	35		42
8
9
10
11	7	6	TRUE	G6	G6	1
12	6	6	TRUE	F6	F6	2
13	5	6	TRUE	E6	E6	3
14	4	6	TRUE	D6	D6	4
15	3	6	TRUE	C6	C6	5
16	2	6	TRUE	B6	B6	6
17	1	6	TRUE	A6	A6	7
18	7	5	TRUE	G5	G5	8
19	6	5	FALSE		E5	9
20	5	5	TRUE	E5	D5	10
21	4	5	TRUE	D5	C5	11
22	3	5	TRUE	C5	B5	12
23	2	5	TRUE	B5	A5	13
24	1	5	TRUE	A5	G4	14
25	7	4	TRUE	G4	F4	15
26	6	4	TRUE	F4	E4	16
27	5	4	TRUE	E4	D4	17
28	4	4	TRUE	D4	C4	18
29	3	4	TRUE	C4	A4	19
30	2	4	FALSE		G3	20
31	1	4	TRUE	A4	F3	21
32	7	3	TRUE	G3	E3	22
33	6	3	TRUE	F3	D3	23
34	5	3	TRUE	E3	C3	24
35	4	3	TRUE	D3	B3	25
36	3	3	TRUE	C3	A3	26
37	2	3	TRUE	B3	G2	27
38	1	3	TRUE	A3	F2	28
39	7	2	TRUE	G2	E2	29
40	6	2	TRUE	F2	C2	30
41	5	2	TRUE	E2	B2	31
42	4	2	FALSE		A2	32
43	3	2	TRUE	C2	G1	33
44	2	2	TRUE	B2	F1	34
45	1	2	TRUE	A2	E1	35
46	7	1	TRUE	G1	D1	36
47	6	1	TRUE	F1	C1	37
48	5	1	TRUE	E1	B1	38
49	4	1	TRUE	D1	A1	39
50	3	1	TRUE	C1		40
51	2	1	TRUE	B1		41
52	1	1	TRUE	A1		42
Sheet1

Cell Formulas
Range	Formula
J1:J7,L1:L7,N1:N7,P1:P7,R1:R7,T1:T7	J1	=INDEX($E$11:$E$52,(MOD(ROW(A1)-1,7)+1)+(INT((COLUMN(A1)-1)/2)*7))
K1:K7,M1:M7,O1:O7,Q1:Q7,S1:S7,U1:U7	K1	=INDEX($F$11:$F$52,(MOD(ROW(B1)-1,7)+1)+(INT((COLUMN(B1)-1)/2)*7))
A11:A52	A11	=MOD(7-ROW(A1),7)+1
B11:B52	B11	=6-INT((ROW(A1)-1)/7)
C11:C52	C11	=INDEX($B$1:$H$6,B11,A11)
D11:D52	D11	=IF(C11,CHAR(A11+64)&B11,"")
E11:E52	E11	=IFERROR(IF(ROWS($E$11:E11)>COUNTA($D$11:$D$52),"",INDEX($D$11:$D$52,SMALL(IF($D$11:$D$52<>"",ROW($D$11:$D$52)-ROW($D$11)+1),ROWS($E$11:E11)))),"")
F11:F52	F11	=ROWS($F$11:F11)

 

[choks90233]

I really like this thank you so much!