Formula. Fin+nd a value in a table

[Alejandro Rodriguez]

Hello,

Which formula I need to accomplish the below results.

I tried with

=INDEX(INC!E:E,MATCH(A2,INC!O:X,0))

but doesnt work

Thank you for your help.


MY TABLE

Excel 2003
	E	J	K	O	P	Q	R	S	T	U	V	W	X
3
4
5	DESC	2010	2012	1	2	3	4	5	6	7	8	9	10
6	azul	100	500										A276549870
7	amarillo		300		A2
8	verde	500			A1
9	blanco		1	A
10	turquesa	4	0		A5
11	morado				A3
12	naranjado	8	36		A4
13	negro	20
14	gris		45			E35
15	rosado	90			E3
16	pastel	1000	56	E
17	fucsia	100					E312
18	mono	200			E2
19	gato		78
20	conejo	300				E31
21	tigre	50			E8
22	leon		56				E311
23	gorila	10					E315
24	chimpance		100					E3121
25	oso	0		H
26	tiburon	0	20								H4567143
27	avion	20		K
28	barco	30	34		K1
29	camion	60	23		K2
30
INC

RESULT REQUIRED

Excel 2003
	A	B	C	D
1	INDEX	DESC	2010	2012
2	A	blanco		1
3	E	pastel	1000	56
4	H	oso	0
5	K	avion	20
6	A1	verde	500
7	A2	amarillo		300
8	A3	morado
9	A4	naranjado	8	36
10	A5	turquesa	4	0
11	E2	mono	200
12	E3	rosado	90
13	E8	tigre	50
14	K1	barco	30	34
15	K2	camion	60	23
16	E31	conejo	300
17	E35	gris		45
18	E311	leon		56
19	E312	fucsia	100
20	E315	gorila	10
21	E3121	chimpance		100
22	H4567143	tiburon	0	20
23	A276549870	azul	100	500
24
ORX

 

[Aladin Akyurek]

E:X of INC houses the data.

A:D of the destination sheet houses the processing...

A2 of the destination sheet, control+shift+enter, not just enter, and copy down:

=LOOKUP(REPT("z",255),INDEX(INC!$H$6:$Q$29,
  MIN(IF(INC!$E$6:$E$29=$B2,IF(INC!$F$6:$F$29=$C2,IF(INC!$G$6:$G$29=$D2,
  ROW(INC!$H$6:$Q$29)-ROW(INC!$H$6)+1)))),0))

 

[Peter_SSs]

My suggestion requires a number of helper rows/columns. These could be placed elsewhere and hidden once populated.

O4 copied across to X4.

Excel Workbook
	E	J	K	O	P	Q	R	S	T	U	V	W	X
4				1	5	15	17	20	21	21	21	22	22
5	DESC	2010	2012	1	2	3	4	5	6	7	8	9	10
6	azul	100	500										A276549870
7	amarillo		300		A2
8	verde	500			A1
9	blanco		1	A
10	turquesa	4	0		A5
11	morado				A3
12	naranjado	8	36		A4
13	negro	20
14	gris		45			E35
15	rosado	90			E3
16	pastel	1000	56	E
17	fucsia	100					E312
18	mono	200			E2
19	gato		78
20	conejo	300				E31
21	tigre	50			E8
22	leon		56				E311
23	gorila	10					E315
24	chimpance		100					E3121
25	oso	0		H
26	tiburon	0	20								H4567143
27	avion	20		K
28	barco	30	34		K1
29	camion	60	23		K2
INC

All row 2 formulas copied down except C2 which is copied across to D2 and down.
G2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	INDEX	DESC	2010	2012		Col	Row
2	A	blanco		1		1	4
3	E	pastel	1000	56		1	11
4	H	oso	0			1	20
5	K	avion	20			1	22
6	A2	amarillo		300		2	2
7	A1	verde	500			2	3
8	A5	turquesa	4	0		2	5
9	A3	morado				2	6
10	A4	naranjado	8	36		2	7
11	E3	rosado	90			2	10
12	E2	mono	200			2	13
13	E8	tigre	50			2	16
14	K1	barco	30	34		2	23
15	K2	camion	60	23		2	24
16	E35	gris		45		3	9
17	E31	conejo	300			3	15
18	E312	fucsia	100			4	12
19	E311	leon		56		4	17
20	E315	gorila	10			4	18
21	E3121	chimpance		100		5	19
22	H4567143	tiburon	0	20		8	21
23	A276549870	azul	100	500		10	1
24
ORX

 

[Rick Rothstein]

Since the 2010 and 2012 header look like year values from the past, I'm guessing the table you showed for "RESULT REQUIRED" is something that does not require the instant and continual updating that a formula solution would provide, so I am guessing that a macro solution would be usable for your task. Given that, here is such a macro...

Sub CollapseAndSortData()
  Dim R As Long, C As Long, MaxLen As Long
  Dim WSin As Worksheet, WSout As Worksheet
  Dim DataIn As Variant, DataOut As Variant
  Set WSin = Sheets("[COLOR=#FF0000][B]Sheet1[/B][/COLOR]")
  Set WSout = Sheets("[COLOR=#008000][B]Sheet2[/B][/COLOR][COLOR=#FF0000][/COLOR]")
  DataIn = WSin.Range("E5", WSin.Cells(WSin.Cells(Rows.Count, "E").End(xlUp).Row, _
                            WSin.Cells(5, Columns.Count).End(xlToLeft).Column))
  DataOut = WSin.Range("D5").Resize(UBound(DataIn), 4)
  DataOut(1, 1) = "INDEX"
  MaxLen = Evaluate("MAX(LEN(" & WSin.Name & "!H6:Q" & WSin.Cells(Rows.Count, "E").End(xlUp).Row & "))")
  For R = 2 To UBound(DataIn)
    For C = 4 To 13
      If Len(DataIn(R, C)) Then
        DataOut(R, 1) = Format(DataIn(R, C), String(MaxLen, "@"))
        Exit For
      End If
    Next
  Next
  With WSout.Range("A1").Resize(UBound(DataOut), UBound(DataOut, 2))
    .Value = DataOut
    .Sort Range("A1"), Header:=xlYes
    .Replace " ", "", xlPart
    On Error Resume Next
    .Resize(, 1).SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

Note: You need to change the two sheet names I highlighted in color above... the red one to the sheet name where your data is located and the green one to the sheet name where you want your data outputted to.


HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CollapseAndSortData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Aladin Akyurek]

In post #2, I assumed A2 should be the result while B2, C2, and D2 as the look up values.

If A2 is in fact the look up value and B2:D2 are the results, try...

In R6 of INC just enter and copy down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(REPT("z",255),H6:Q6)))

In B2 of the destination sheet, just enter, copy across, and down:

=IF(INDEX(INC!E$6:E$29,MATCH($A2,INC!$R$6:$R$29,0))="","",
  INDEX(INC!E$6:E$29,MATCH($A2,INC!$R$6:$R$29,0)))

 

[Peter_SSs]

In post #2, I assumed A2 should be the result while B2, C2, and D2 as the look up values.

If A2 is in fact the look up value and B2:D2 are the results, try...

Aladin

Looking back at post #1, you may well be right with one of those assumptions. I assumed the whole table needed to be created.


Rick,

Your code highlighted to me that my formula solution did not order the results in exactly the same way the OP has. If the whole table does have to be created and that order is important, then I don't see a feasible formula solution. (Too hard for me anyway. )

Need some OP feedback on whether only part of the results table needs to be created (& if so which part) or, if the whole table, whether the exact order is critical.

I think you have just overlooked this red dot .Sort .Range("A1"), Header:=xlYes

I think you may also ave missed that there were a number of columns hidden in the OP's screen shot of the data sheet.

My attempted code solution, if the whole table needs to be created** is below.

** I have assumed that the 'ORX' sheet exists and already holds at least headings but nothing else that needs to be kept.

Sub Make_Results()
  Dim a, b, aRws
  Dim i As Long, j As Long, k As Long, rws As Long
 
  Const DataCols As String = "5 10 11 15 16 17 18 19 20 21 22 23 24"
  
  With Sheets("INC")
    aRws = Evaluate("row(6:" & .Cells(.Rows.Count, 5).End(xlUp).Row & ")")
    a = Application.Index(.Cells, aRws, Split(DataCols))
  End With
  rws = UBound(aRws)
  ReDim b(1 To rws * 10, 1 To 5)
  For j = 4 To 13
    For i = 1 To rws
      If Len(a(i, j)) Then
        k = k + 1
        b(k, 1) = a(i, j)
        b(k, 2) = a(i, 1)
        b(k, 3) = a(i, 2)
        b(k, 4) = a(i, 3)
        b(k, 5) = j
      End If
    Next i
  Next j
  With Sheets("ORX")
    .UsedRange.Offset(1).ClearContents
    .Range("A2").Resize(k, 5).Value = b
    .UsedRange.Sort Key1:=.Range("E2"), Order1:=xlAscending, Key2:=.Range("A2"), Order2:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    .Range("E2").Resize(k).ClearContents
  End With
End Sub

 

[Rick Rothstein]

Rick,

I think you have just overlooked this red dot .Sort .Range("A1"), Header:=xlYes

I think you may also ave missed that there were a number of columns hidden in the OP's screen shot of the data sheet.

You are right on both counts... I missed the "dot" in front of Range and I did not pay any attention to the column headers as I thought that nice colored table header on Row 5 delineated a contiguous table, so I simply copy/pasted the data and wrote my code from there. Thanks for noting those lapses on my part. My next message will contain corrected code.

 

[Rick Rothstein]

The code I posted in Message #4 will not work for the reasons Peter_SSs alluded to in Message #6. Here is my revised code which actually uses the range you show your data to be in...

Sub CollapseAndSortData()
  Dim R As Long, C As Long, MaxLen As Long
  Dim WSin As Worksheet, WSout As Worksheet
  Dim DataIn As Variant, DataOut As Variant
  Set WSin = Sheets("Sheet4")
  Set WSout = Sheets("Sheet5")
  DataIn = WSin.Range("O5", WSin.Cells(WSin.Cells(Rows.Count, "E").End(xlUp).Row, _
                                       WSin.Cells(5, Columns.Count).End(xlToLeft).Column))
  WSout.Range("B1").Resize(UBound(DataIn), 3) = Application.Index(WSin.Cells, Evaluate("Row(5:" & _
                                                UBound(DataIn) + 4 & ")"), Split("5 10 11"))
  ReDim DataOut(1 To UBound(DataIn), 1 To 1)
  DataOut(1, 1) = "INDEX"
  MaxLen = Evaluate("MAX(LEN(" & WSin.Name & "!O6:X" & UBound(DataIn) + 4 & "))")
  For R = 2 To UBound(DataIn)
    For C = 1 To UBound(DataIn, 2)
      If Len(DataIn(R, C)) Then
        DataOut(R, 1) = Format(DataIn(R, C), String(MaxLen, "@"))
        Exit For
      End If
    Next
  Next
  With WSout.Range("A1").Resize(UBound(DataOut), 1)
    .Value = DataOut
    .Resize(, 4).Sort .Range("A1"), Header:=xlYes
    .Replace " ", "", xlPart
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub

 

[Alejandro Rodriguez]

gentlemen,

Thank you for your time and your help.
And sorry for not being clear enough.

More than happy with your different options. I will try all of them.

Once again, thank you so much.

 

[Rick Rothstein]

The code I posted in Message #4 will not work for the reasons Peter_SSs alluded to in Message #6. Here is my revised code which actually uses the range you show your data to be in...

Sub CollapseAndSortData()
  Dim R As Long, C As Long, MaxLen As Long
  Dim WSin As Worksheet, WSout As Worksheet
  Dim DataIn As Variant, DataOut As Variant
  Set WSin = Sheets("[COLOR=#FF0000][B]Sheet1[/B][/COLOR]")
  Set WSout = Sheets("[COLOR=#008000][B]Sheet2[/B][/COLOR]")
  DataIn = WSin.Range("O5", WSin.Cells(WSin.Cells(Rows.Count, "E").End(xlUp).Row, _
                                       WSin.Cells(5, Columns.Count).End(xlToLeft).Column))
  WSout.Range("B1").Resize(UBound(DataIn), 3) = Application.Index(WSin.Cells, Evaluate("Row(5:" & _
                                                UBound(DataIn) + 4 & ")"), Split("5 10 11"))
  ReDim DataOut(1 To UBound(DataIn), 1 To 1)
  DataOut(1, 1) = "INDEX"
  MaxLen = Evaluate("MAX(LEN(" & WSin.Name & "!O6:X" & UBound(DataIn) + 4 & "))")
  For R = 2 To UBound(DataIn)
    For C = 1 To UBound(DataIn, 2)
      If Len(DataIn(R, C)) Then
        DataOut(R, 1) = Format(DataIn(R, C), String(MaxLen, "@"))
        Exit For
      End If
    Next
  Next
  With WSout.Range("A1").Resize(UBound(DataOut), 1)
    .Value = DataOut
    .Resize(, 4).Sort .Range("A1"), Header:=xlYes
    .Replace " ", "", xlPart
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub

I forgot my note about the sheet names...

Note: You need to change the two sheet names I highlighted in color above... the red one to the sheet name where your data is located and the green one to the sheet name where you want your data outputted to.