# index match to find data?



## fastballfreddy (Jan 1, 2023)

I have data (time) in cells B2:H9. In cells B12:B25 I have Category for each hour that has data. I've been trying to figure out a formula to capture the time in cell C12:C25. I've been trying to use an Index/Match but can't figure it out. Attaching an image of what i have. Any help or direction would be greatly appreciated as i've been searching for a couple days and this is my last resort. Thanks!


----------



## Peter_SSs (Jan 2, 2023)

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’)

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will *allow us to copy/paste* it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


----------



## shinigamilight (Jan 2, 2023)

This is a job of VBA at least in older versions of excel. If you're on 365 maybe someone can provide a formula solution to you, but in the meantime you can use this.


```
Sub displace()
            
        Dim lr, lc As Long
        Dim k, i, p As Integer
        Dim wk As Worksheet
        Set wk = Sheets("tot") ' set the  sheet where you want your output to be placed
        
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
        lc = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Column
        p = 1
        
        For k = 2 To lr
                For i = 2 To lc
                        If Cells(k, i) <> "" Then
                            wk.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = p
                            Cells(k, 1).Copy wk.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
                            Cells(k, i).Copy wk.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
                            p = p + 1
                        End If
                Next i
        Next k

End Sub
```


----------



## Peter_SSs (Jan 2, 2023)

Thanks for updating your version details. 
If you still need help with the problem though, please provide the XL2BB sample data as requested.


----------



## fastballfreddy (Jan 2, 2023)

Thanks Peter! Finally figured it out and got it added.

Thanks shinigamilight! That worked great. I definitely need to learn more about Dim macros as that looks like I can benefit a lot from them. Additional question (hope it's ok) and attaching the xl2bb files. Is there a formula or macro to get the hours in column D of worksheet "tot"? for example system issue would show 7:00am for the 1st entry and system issue would show 8:00am for the 2nd entry and so on. 

Once again thank you so much! 


Excel - index match.xlsmABCDEFGHIJKLMN1Category7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM2System Issue0:020:273Work0:331:001:000:304Meeting0:265Work0:040:156Break 10:157Work0:300:148Meeting0:169Lunch0:3010Work1:001:001:001:001:001:001:00sheets

Excel - index match.xlsmABCD1What I want21System Issue0:027:00 AM32System Issue0:278:00 AM43Work0:338:00 AM54Work1:009:00 AM65Work1:0010:00 AM76Work0:3011:00 AM87Meeting0:2611:00 AM98Work0:0411:00 AM109Work0:1512:00 PM1110Break 10:1512:00 PM1211Work0:3012:00 PM1312Work0:141:00 PM1413Meeting0:161:00 PM1514Lunch0:301:00 PM1615Work0:301:00 PM1716Work0:152:00 PM1817Work0:303:00 PM1918Work0:144:00 PM2019Work0:305:00 PM2120Work0:146:00 PM2221Work0:147:00 PMtot


----------



## Peter_SSs (Jan 2, 2023)

fastballfreddy said:


> Finally figured it out and got it added.


Thanks for the XL2BB sample data and results.
I don't understand your results in 'tot' range C16:C22 in post 5 but in any case would any of this be of use if you wanted a formula approach?

23 01 03.xlsmABCD121System Issue0:027:00 AM32System Issue0:278:00 AM43Work0:338:00 AM54Work1:009:00 AM65Work1:0010:00 AM76Work0:3011:00 AM87Meeting0:2611:00 AM98Work0:0411:00 AM109Work0:1512:00 PM1110Break 10:1512:00 PM1211Work0:3012:00 PM1312Work0:141:00 PM1413Meeting0:161:00 PM1514Lunch0:301:00 PM1615Work1:001:00 PM1716Work1:002:00 PM1817Work1:003:00 PM1918Work1:004:00 PM2019Work1:005:00 PM2120Work1:006:00 PM2221Work1:007:00 PMtotCell FormulasRangeFormulaC2:C22C2=INDIRECT("sheets!"&TEXT(AGGREGATE(15,6,(ROW(sheets!B$2:N$10)*10^6+COLUMN(sheets!B$2:N$10))/(sheets!B$2:N$10<>""),ROWS(C$2:C2)),"R000000C000000"),0)D2:D22D2=INDEX(sheets!$1:$1,AGGREGATE(15,6,IF(sheets!$B$2:$N$10<>"",COLUMN(sheets!$B$1:$N$2)),ROWS(D$2:D2)))


----------



## shinigamilight (Jan 3, 2023)

fastballfreddy said:


> Thanks Peter! Finally figured it out and got it added.
> 
> Thanks shinigamilight! That worked great. I definitely need to learn more about Dim macros as that looks like I can benefit a lot from them. Additional question (hope it's ok) and attaching the xl2bb files. Is there a formula or macro to get the hours in column D of worksheet "tot"? for example system issue would show 7:00am for the 1st entry and system issue would show 8:00am for the 2nd entry and so on.
> 
> ...










```
Sub displace_2()
            
        Dim lr, lc As Long
        Dim k, i, p As Integer
        Dim wk As Worksheet
        Set wk = Sheets("tot") ' set the  sheet where you want your output to be placed
        
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
        lc = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Column
        p = 1
        
        For k = 2 To lr
                For i = 2 To lc
                        If Cells(k, i) <> "" Then
                            wk.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = p
                            Cells(k, 1).Copy wk.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
                            Cells(k, i).Copy wk.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
                            Cells(1, i).Copy wk.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
                            p = p + 1
                        End If
                Next i
        Next k

End Sub
```


----------



## jdellasala (Jan 3, 2023)

YAS:
Bring the first table:
Book1ABCDEFGHIJKLMN1Category7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM2System Issue12:02 AM12:27 AM3Work12:33 AM1:00 AM1:00 AM12:30 AM4Meeting12:26 AM5Work12:04 AM12:15 AM6Break 112:15 AM7Work12:30 AM12:14 AM8Meeting12:16 AM9Lunch12:30 AM10Work1:00 AM1:00 AM1:00 AM1:00 AM1:00 AM1:00 AM1:00 AMSheet5
into Power Query (converting it into a Table), and transform it:

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Category"}, "Hour", "Minute"),
    ChangedType = Table.TransformColumnTypes(UnpivotedOtherColumns,{{"Minute", type time}, {"Category", type text}, {"Hour", type time}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Item#", 1, 1, Int64.Type),
    ReorderedColumns = Table.ReorderColumns(AddedIndex,{"Item#", "Category", "Minute", "Hour"})
in
    ReorderedColumns
```
Resulting in this:
Book1ABCD13Item#CategoryMinuteHour141System Issue0:027:00 AM152System Issue0:278:00 AM163Work0:338:00 AM174Work1:009:00 AM185Work1:0010:00 AM196Work0:3011:00 AM207Meeting0:2611:00 AM218Work0:0411:00 AM229Work0:1512:00 PM2310Break 10:1512:00 PM2411Work0:3012:00 PM2512Work0:141:00 PM2613Meeting0:161:00 PM2714Lunch0:301:00 PM2815Work1:001:00 PM2916Work1:002:00 PM3017Work1:003:00 PM3118Work1:004:00 PM3219Work1:005:00 PM3320Work1:006:00 PM3421Work1:007:00 PMSheet5
All code generated by the Power Query UI. I did manually change column names after performing the Unpivot and Index steps.


----------



## fastballfreddy (Jan 5, 2023)

Thank you everyone!! I appreciate each of you taking the time to review and give me a solution to my problem. It's greatly appreciated! I love all 3 solutions. The macro works great and the formula as well. I've never used Power Query before and after playing around with it and understanding I think it's my best option for the volume of data. I really appreciate the help and this has opened many doors to learn more about excel which is exciting so thank you!!


----------



## Peter_SSs (Jan 6, 2023)

You're welcome. Glad we could help. Thanks for the follow-up.


----------

