Comparing historical data

[taher9990]

Dears

I need your help and support on this please

I am trying to fnd a formula or VBA code or using pivot table or even excel 2016 pivot table to do the following:

To compare grades for each ID and show the result in the result column, if grade for ID 1 in 04/01/2016 greater than grade for ID 1 in 03/01/2016 then show something like. True. and so on so forth for each row and ID.

Date	Id	Grade	Result
01/01/2016	1	2
02/01/2016	1	4
03/01/2016	1	7
04/01/2016	1	9
01/01/2016	2	3
02/01/2016	2	7
03/01/2016	2	9
04/01/2016	2	2
01/01/2016	3	4
02/01/2016	3	6
03/01/2016	3	10
04/01/2016	3	4

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

 

[michaelsmith559]

I have a macro that I think will do what you want. In my example, I list the id and times to repeat in separate columns. When you run the macro, select the id and times to repeat values. In my sheet you would select f2:g4, then you will be asked to select a column for the id's. Select b2. I also assumed you were comparing only the same id's. Here is the screenshot before, followed by macro, and then screenshot after macro runs. Macro credit goes to kutools for excel. I modified one of their macros.


<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Id</td><td style="text-align: center;;">Grade</td><td style="text-align: center;;">Result</td><td style="text-align: right;;"></td><td style="text-align: center;;">Id</td><td style="text-align: center;;">Repeat</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">1/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">2/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">3/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">4/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">2/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">3/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">4/1/2016</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Macro:

Sub Compare_Historical_Data()

Dim Rng As Range, i As Integer
Dim InputRng As Range, OutRng As Range
Dim firstRow As Long, lastRow As Long

Range(Range("B2"), Range("B2").End(xlDown)).Select
Selection.ClearContents
Range("E1").Select

Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Select Id and Times to Repeat:", InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Select Column for Id:", Type:=8)
Set OutRng = OutRng.Range("A1")

For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    lastRow = Cells(1048576, 2).End(xlUp).Row
    firstRow = lastRow - (xNum - 1)
        For i = lastRow To firstRow Step -1
            If Cells(i, 3).Value > Cells(i - 1, 3).Value Then
                Cells(i, 4).Value = True
                Else
                Cells(i, 4).Value = False
            End If
        Next i
    Set OutRng = OutRng.Offset(xNum, 0)
Next

End Sub

After macro runs:

Excel 2012
	A	B	C	D	E	F	G
1	Date	Id	Grade	Result		Id	Repeat
2	1/1/2016	1	2	FALSE		1	4
3	2/1/2016	1	4	TRUE		2	4
4	3/1/2016	1	7	TRUE		3	4
5	4/1/2016	1	9	TRUE
6	1/1/2016	2	3	FALSE
7	2/1/2016	2	7	TRUE
8	3/1/2016	2	9	TRUE
9	4/1/2016	2	2	FALSE
10	1/1/2016	3	4	TRUE
11	2/1/2016	3	6	TRUE
12	3/1/2016	3	10	TRUE
13	4/1/2016	3	4	FALSE
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

 

[michaelsmith559]

Sorry, need to correct macro above. Here is the corrected version:

Sub Compare_Historical_Data()

Dim Rng As Range, i As Integer
Dim InputRng As Range, OutRng As Range
Dim firstRow As Long, lastRow As Long

Range(Range("B2"), Range("B2").End(xlDown)).Select
Selection.ClearContents
Range("E1").Select

Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Select Id and Times to Repeat:", InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Select Column for Id:", Type:=8)
Set OutRng = OutRng.Range("A1")

For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    lastRow = Cells(1048576, 2).End(xlUp).Row
    firstRow = lastRow - (xNum - 2)
        For i = lastRow To firstRow Step -1
            If Cells(i, 3).Value > Cells(i - 1, 3).Value Then
                Cells(i, 4).Value = True
                Else
                Cells(i, 4).Value = False
            End If
        Next i
    Set OutRng = OutRng.Offset(xNum, 0)
Next

End Sub

 

[michaelsmith559]

Here is a screenshot for macro in post #3:

Excel 2012
	A	B	C	D	E	F	G
1	Date	Id	Grade	Result		Id	Repeat
2	1/1/2016	1	2			1	4
3	2/1/2016	1	4	TRUE		2	4
4	3/1/2016	1	7	TRUE		3	4
5	4/1/2016	1	9	TRUE
6	1/1/2016	2	3
7	2/1/2016	2	7	TRUE
8	3/1/2016	2	9	TRUE
9	4/1/2016	2	2	FALSE
10	1/1/2016	3	4
11	2/1/2016	3	6	TRUE
12	3/1/2016	3	10	TRUE
13	4/1/2016	3	4	FALSE
Sheet1

 

[taher9990]

Man that's exactly what I want, thanks so much, I will test it and get back to you dear friend