IF > 0 THEN return corresponding Column & Row Header; for times the value occurred on each date

[qadeerahmed]

I' have a question somewhat similar to this post, but with some difference.

https://www.mrexcel.com/forum/excel...column-header-row-header-each-occurrence.html

	01-JAN-17	02-JAN-17	03-JAN-17	04-JAN-17	05-JAN-17	06-JAN-17	07-JAN-17
99XX1002	0	1	0	3	1	1	0
99XX1003	0	2	0	1	3	0	0
99XX1004	0	0	0	0	0	0	1
99RN1005	2	0	0	5	0	0	0

<tbody>
</tbody>

what I 'm looking is... IF > 0 THEN return corresponding Column & Row Header; for times the value occurred on each date e.g. 99xx1002 occurred 1 time on "02-Jan-17" and 3 times on "04-Jan-17 and 1 time on 05/06 jan.... I want to put this into tabular form like.... 99xx1002 02-jan-17 99xx1002 04-Jan-17 99xx1002 04-Jan-17 99xx1002 04-Jan-17 99xx1002 05-Jan-17 99xx1002 06-jan-17 99xx1003 02-jan-17 99xx1003 02-jan-17 <tbody> </tbody> Appreciate for your time and help Thanks!

<tbody>
</tbody><colgroup><col span="8"></colgroup>

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H
1		01-Jan-17	02-Jan-17	03-Jan-17	04-Jan-17	05-Jan-17	06-Jan-17	07-Jan-17
2	99XX1002	0	1	0	3	1	1	0
3	99XX1003	0	2	0	1	3	0	0
4	99XX1004	0	0	0	0	0	0	1
5	99RN1005	2	0	0	5	0	0	0
Sheet1

This code will put a new table underneath:

Public Sub WBD2017052301()

Dim lastCol As Long
Dim lastRow As Long
Dim thisCol As Long
Dim thisRow As Long
Dim cellValue As Variant
Dim nextRow As Long

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

nextRow = lastRow + 2

For thisRow = 2 To lastRow
    For thisCol = 2 To lastCol
        cellValue = Cells(thisRow, thisCol).Value
        While cellValue > 0
            Cells(nextRow, 1) = Cells(thisRow, 1)
            Cells(nextRow, 1).NumberFormat = Cells(thisRow, 1).NumberFormat
            Cells(nextRow, 2) = Cells(1, thisCol)
            Cells(nextRow, 2).NumberFormat = Cells(1, thisCol).NumberFormat
            nextRow = nextRow + 1
            cellValue = cellValue - 1
        Wend
    Next thisCol
Next thisRow
    
End Sub

Like this:

Book1
	A	B
7	99XX1002	02-Jan-17
8	99XX1002	04-Jan-17
9	99XX1002	04-Jan-17
10	99XX1002	04-Jan-17
11	99XX1002	05-Jan-17
12	99XX1002	06-Jan-17
13	99XX1003	02-Jan-17
14	99XX1003	02-Jan-17
15	99XX1003	04-Jan-17
16	99XX1003	05-Jan-17
17	99XX1003	05-Jan-17
18	99XX1003	05-Jan-17
19	99XX1004	07-Jan-17
20	99RN1005	01-Jan-17
21	99RN1005	01-Jan-17
22	99RN1005	04-Jan-17
23	99RN1005	04-Jan-17
24	99RN1005	04-Jan-17
25	99RN1005	04-Jan-17
26	99RN1005	04-Jan-17
Sheet1

WBD

 

[qadeerahmed]

Thanks WBD! really helped a lot!