Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Thank you for your help.


    MY TABLE
    Excel 2003
    EJKOPQRSTUVWX
    3
    4
    5DESC2010201212345678910
    6azul100500A276549870
    7amarillo300A2
    8verde500A1
    9blanco1A
    10turquesa40A5
    11moradoA3
    12naranjado836A4
    13negro20
    14gris45E35
    15rosado90E3
    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
    8A3morado
    9A4naranjado836
    10A5turquesa40
    11E2mono200
    12E3rosado90
    13E8tigre50
    14K1barco3034
    15K2camion6023
    16E31conejo300
    17E35gris45
    18E311leon56
    19E312fucsia100
    20E315gorila10
    21E3121chimpance100
    22H4567143tiburon020
    23A276549870azul100500
    24

    ORX




  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default 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))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default 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

     EJKOPQRSTUVWX
    4   151517202121212222
    5DESC2010201212345678910
    6azul100500         A276549870
    7amarillo 300 A2        
    8verde500  A1        
    9blanco 1A         
    10turquesa40 A5        
    11morado   A3        
    12naranjado836 A4        
    13negro20           
    14gris 45  E35       
    15rosado90  E3        
    16pastel100056E         
    17fucsia100    E312      
    18mono200  E2        
    19gato 78          
    20conejo300   E31       
    21tigre50  E8        
    22leon 56   E311      
    23gorila10    E315      
    24chimpance 100    E3121     
    25oso0 H         
    26tiburon020       H4567143  
    27avion20 K         
    28barco3034 K1        
    29camion6023 K2        

    Spreadsheet Formulas
    CellFormula
    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

     ABCDEFG
    1INDEXDESC20102012 ColRow
    2Ablanco 1 14
    3Epastel100056 111
    4Hoso0  120
    5Kavion20  122
    6A2amarillo 300 22
    7A1verde500  23
    8A5turquesa40 25
    9A3morado   26
    10A4naranjado836 27
    11E3rosado90  210
    12E2mono200  213
    13E8tigre50  216
    14K1barco3034 223
    15K2camion6023 224
    16E35gris 45 39
    17E31conejo300  315
    18E312fucsia100  412
    19E311leon 56 417
    20E315gorila10  418
    21E3121chimpance 100 519
    22H4567143tiburon020 821
    23A276549870azul100500 101
    24       

    Spreadsheet Formulas
    CellFormula
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default 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
        .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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default 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)))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Formula. Fin+nd a value in a table

    Quote Originally Posted by Aladin Akyurek View Post
    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.

    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Formula. Fin+nd a value in a table

    Quote Originally Posted by Peter_SSs View Post
    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's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default 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
        .Resize(, 4).Sort .Range("A1"), Header:=xlYes
        .Replace " ", "", xlPart
        .SpecialCells(xlBlanks).EntireRow.Delete
      End With
    End Sub
    Last edited by Rick Rothstein; Oct 25th, 2014 at 06:37 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Jul 2005
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula. Fin+nd a value in a table

    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.


  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Formula. Fin+nd a value in a table

    Quote Originally Posted by Rick Rothstein View Post
    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
        .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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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