Thanks:  0
Likes:  0

# Thread: Formula. Fin+nd a value in a table

1. ## Formula. Fin+nd a value in a table

Hello,

Which formula I need to accomplish the below results.

I tried with
Code:
`=INDEX(INC!E:E,MATCH(A2,INC!O:X,0))`
but doesnt work

MY TABLE
Excel 2003
EJKOPQRSTUVWX
3
4
5DESC2010201212345678910
6azul100500A276549870
7amarillo300A2
8verde500A1
9blanco1A
10turquesa40A5
13negro20
14gris45E35
16pastel100056E
17fucsia100E312
18mono200E2
19gato78
20conejo300E31
21tigre50E8
22leon56E311
23gorila10E315
24chimpance100E3121
25oso0H
26tiburon020H4567143
27avion20K
28barco3034K1
29camion6023K2
30

INC

RESULT REQUIRED
Excel 2003
ABCD
1INDEXDESC20102012
2Ablanco1
3Epastel100056
4Hoso0
5Kavion20
6A1verde500
7A2amarillo300
10A5turquesa40
11E2mono200
13E8tigre50
14K1barco3034
15K2camion6023
16E31conejo300
17E35gris45
18E311leon56
19E312fucsia100
20E315gorila10
21E3121chimpance100
22H4567143tiburon020
23A276549870azul100500
24

ORX

2. ## Re: Formula. Fin+nd a value in a table

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:
Code:
```=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))
```

3. ## Re: Formula. Fin+nd a value in a table

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

O4 copied across to X4.

INC

 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

 Cell Formula O4 =COUNTA(\$O6:O29)-COUNTA(O6:O29)+1

Excel tables to the web >> Excel Jeanie HTML 4

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.

ORX

 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

 Cell Formula A2 =IF(F2="","",INDEX(INC!O\$6:X\$29,G2,F2)) B2 =IF(F2="","",INDEX(INC!E\$6:E\$29,G2)) C2 =IF(\$F2="","",IF(INDEX(INC!J\$6:J\$29,\$G2)="","",INDEX(INC!J\$6:J\$29,\$G2))) F2 =IF(ROWS(F\$2:F2)>COUNTA(INC!\$O\$6:\$X\$29),"",MATCH(ROWS(F\$2:F2),INC!O\$4:X\$4)) G2 {=IF(F2="","",SMALL(IF(INDEX(INC!O\$6:X\$29,0,F2)<>"",ROW(INC!O\$6:O\$29)),ROWS(G\$2:G2)-LOOKUP(ROWS(G\$2:G2),INC!O\$4:X\$4)+1)-ROW(INC!O\$6)+1)}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

4. ## Re: Formula. Fin+nd a value in a table

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...
Code:
```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("Sheet1")
Set WSout = Sheets("Sheet2")
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
.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.

5. ## Re: Formula. Fin+nd a value in a table

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:
Code:
```=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:
Code:
```=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)))
```

6. ## Re: Formula. Fin+nd a value in a table

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...

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.

Code:
```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```

7. ## Re: Formula. Fin+nd a value in a table

Originally Posted by Peter_SSs
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.

8. ## Re: Formula. Fin+nd a value in a table

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...
Code:
```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
.Replace " ", "", xlPart
.SpecialCells(xlBlanks).EntireRow.Delete
End With
End Sub```

9. ## Re: Formula. Fin+nd a value in a table

gentlemen,

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.

10. ## Re: Formula. Fin+nd a value in a table

Originally Posted by 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...
Code:
```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("Sheet1")
Set WSout = Sheets("Sheet2")
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
.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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•