Index Match or VBA Assistance

[Bwiles96]

*** I was unable to get the L2BB to work so the best I can do is provide screenshots and I apologize for that. ***


I have two sheets, one labeled "Stored Data" and one labeled "Output"

• What I am needing is to find the intersection of Batch Ticket# 1 and Test 1 (in this instance it would be 9) from the 'Stored Data' spreadsheet and return that value (again, 9) to the row of the batch ticket on the 'Output'

Can't get any formula I've tried to work so I am happy to use VBA or a formula or whatever magic works at this point. I am at a loss here.

Sheets Below:

Stored Data:

Output:

 

[Jeffrey Mahoney]

This is the closest I could come to getting a formula based on the odd format of the data you're trying to retrieve. Can you not combine those tables into one on the stored data sheet?

Book2
	A	B	C	D	E	F
1	Batch Ticket #	Test 1	Test 2	Test 3	Test 4	Test 5
2	1	9	10	11	12	13
3	2	14	15	16	17	18
4	3	19	20	21	22	23
5	4	24	25	26	27	28
6	5	29	30	31	32	33
7	6	34	35	36	37	38
8	7	39	40	41	42	43
9	8	44	45	46	4	48
Output

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(INDEX('Stored Data'!$C$3:$F$7,MATCH(Output!B$1,'Stored Data'!$B$3:$B$7,0),MATCH(Output!$A2,'Stored Data'!$C$1:$F$1,0)),"")
B6:F9	B6	=IFERROR(INDEX('Stored Data'!$C$14:$F$18,MATCH(Output!B$1,'Stored Data'!$B$14:$B$18,0),MATCH(Output!$A6,'Stored Data'!$C$12:$F$12,0)),"")

 

[rpaulson]

try this on a copy of your sheet.

Sub do_it()
For p = 1 To 12 Step 11 'adjust range as needed
    For c = 3 To 6 ' batch numbers
        For test = p + 2 To p + 6 'each test
            Worksheets("Output").Cells(Cells(p, c).Value + 1, test - p) = Cells(test, c)
        Next test
    Next c
Next p
End Sub

 

[Bwiles96]

This is the closest I could come to getting a formula based on the odd format of the data you're trying to retrieve. Can you not combine those tables into one on the stored data sheet?

Book2
	A	B	C	D	E	F
1	Batch Ticket #	Test 1	Test 2	Test 3	Test 4	Test 5
2	1	9	10	11	12	13
3	2	14	15	16	17	18
4	3	19	20	21	22	23
5	4	24	25	26	27	28
6	5	29	30	31	32	33
7	6	34	35	36	37	38
8	7	39	40	41	42	43
9	8	44	45	46	4	48
Output

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(INDEX('Stored Data'!$C$3:$F$7,MATCH(Output!B$1,'Stored Data'!$B$3:$B$7,0),MATCH(Output!$A2,'Stored Data'!$C$1:$F$1,0)),"")
B6:F9	B6	=IFERROR(INDEX('Stored Data'!$C$14:$F$18,MATCH(Output!B$1,'Stored Data'!$B$14:$B$18,0),MATCH(Output!$A6,'Stored Data'!$C$12:$F$12,0)),"")

This is the closest I could come to getting a formula based on the odd format of the data you're trying to retrieve. Can you not combine those tables into one on the stored data sheet?

Book2
	A	B	C	D	E	F
1	Batch Ticket #	Test 1	Test 2	Test 3	Test 4	Test 5
2	1	9	10	11	12	13
3	2	14	15	16	17	18
4	3	19	20	21	22	23
5	4	24	25	26	27	28
6	5	29	30	31	32	33
7	6	34	35	36	37	38
8	7	39	40	41	42	43
9	8	44	45	46	4	48
Output

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(INDEX('Stored Data'!$C$3:$F$7,MATCH(Output!B$1,'Stored Data'!$B$3:$B$7,0),MATCH(Output!$A2,'Stored Data'!$C$1:$F$1,0)),"")
B6:F9	B6	=IFERROR(INDEX('Stored Data'!$C$14:$F$18,MATCH(Output!B$1,'Stored Data'!$B$14:$B$18,0),MATCH(Output!$A6,'Stored Data'!$C$12:$F$12,0)),"")

Unfortunately I cannot. I simplified my actual document.

The tests are all different names - things like A-123-B & D-1298-2.

I changed them to test names "Test1" for simplicity and to not confuse anyone.

Basically each product type has it's own specific tests so I have to break it up this way

But I appreciate the formula. I should be able to modify it to my needs on my sheets

Big help - thanks Jeffrey!

 

[Bwiles96]

try this on a copy of your sheet.

Sub do_it()
For p = 1 To 12 Step 11 'adjust range as needed
    For c = 3 To 6 ' batch numbers
        For test = p + 2 To p + 6 'each test
            Worksheets("Output").Cells(Cells(p, c).Value + 1, test - p) = Cells(test, c)
        Next test
    Next c
Next p
End Sub

This works wonderfully as well. Thank you @rpaulson. Now I just need to figure out how to amend it to match my actual sheet! Lol