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.

VBA Code:

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Category

7:00 AM

8:00 AM

9:00 AM

10:00 AM

11:00 AM

12:00 PM

1:00 PM

2:00 PM

3:00 PM

4:00 PM

5:00 PM

6:00 PM

7:00 PM

2

System Issue

0:02

0:27

3

Work

0:33

1:00

0:30

4

Meeting

0:26

5

Work

0:04

0:15

6

Break 1

0:15

7

Work

0:30

0:14

8

Meeting

0:16

9

Lunch

0:30

10

Work

1:00

sheets

Excel - index match.xlsm

A

B

C

D

1

What I want

2

1

System Issue