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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]01-JAN-17[/TD]
[TD]02-JAN-17[/TD]
[TD]03-JAN-17[/TD]
[TD]04-JAN-17[/TD]
[TD]05-JAN-17[/TD]
[TD]06-JAN-17[/TD]
[TD]07-JAN-17[/TD]
[/TR]
[TR]
[TD]99XX1002[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]99XX1003[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]99XX1004[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]99RN1005[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 512"]
<tbody>[TR]
[TD]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....[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]99xx1002[/TD]
[TD]02-jan-17[/TD]
[/TR]
[TR]
[TD]99xx1002[/TD]
[TD]04-Jan-17[/TD]
[/TR]
[TR]
[TD]99xx1002[/TD]
[TD]04-Jan-17[/TD]
[/TR]
[TR]
[TD]99xx1002[/TD]
[TD]04-Jan-17[/TD]
[/TR]
[TR]
[TD]99xx1002[/TD]
[TD]05-Jan-17[/TD]
[/TR]
[TR]
[TD]99xx1002[/TD]
[TD]06-jan-17[/TD]
[/TR]
[TR]
[TD]99xx1003[/TD]
[TD]02-jan-17[/TD]
[/TR]
[TR]
[TD]99xx1003[/TD]
[TD]02-jan-17[/TD]
[/TR]
</tbody>[/TABLE]

Appreciate for your time and help

Thanks!
[/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]

 

[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!