How to find incident which has changed multiple priority during his life-cycle

[rkvalu]

Hi Team,

I have been brainstorming this from past couple of months. I work in reporting team and during month end I pull all incident report which has changed priority from P1-P2-P3-P4, P2-P3-P4 or P3 to P4. Currently, I am performing it manually (4000+ count). Below is the sample excel where I would highlight in a different color if priority changes from P1-P2-P3-P4, P2-P3-P4 or P3 to P4. Attached is the Spreadsheet.

So basically I want to check column A, if it has more than 2 similar value it should check the final priority in column B based on Column C's updated Date and time and it should return value as P1-P2-P3-P4, P2-P3-P4 or P3 to P4 in Column D. Any help would be appreciated.

 

[BrianJN1]

Sorry but there is no image available for anyone to work with, you need to use one of the suggestions here: https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html
or some shared cloud facility like dropbox.

 

[rkvalu]

Below is the data

[TABLE="width: 268"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Value[/TD]
[TD]Date&Time [/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-04 17:24:46[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-04 17:57:19[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-04 20:55:53[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-05 14:04:26[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-07 14:52:50[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-26 22:34:28[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-27 00:33:59[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-27 12:55:24[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 4[/TD]
[TD]2017-08-02 15:40:53[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-05 15:47:10[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-06 13:00:48[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-14 09:37:34[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-22 00:26:25[/TD]
[/TR]
[TR]
[TD]IMA228506[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-12 13:50:41[/TD]
[/TR]
[TR]
[TD]IMA228506[/TD]
[TD]Priority 4[/TD]
[TD]2017-07-12 16:41:12[/TD]
[/TR]
[TR]
[TD]IMA229891[/TD]
[TD]Priority 3[/TD]
[TD]2017-07-26 21:14:40[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

i believe that this is going to need an array lookup formula to be added to your Col D. Unfortunately I am not the person to assist with this.
If you don't get a response within 24 hours bump this thread.

 

[Peter_SSs]

Below is the data

Could you post that same data again but also include the expected results so that we can see
- exactly what the expected results are, and
- where the result(s) should appear

 

[rkvalu]

Hi Peter,

So basically I will be pulling incident report every month end which has changed the priority in that particular month.

So my excel sheet has two sheets, the first one has raw data with below information. (Sample data)
[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Priority[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA228506[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA228506[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA229891[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA229891[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA241020[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA241020[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA241020[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA241020[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA241489[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA241489[/TD]
[TD]Priority 4[/TD]
[/TR]
</tbody>[/TABLE]

So, in 2nd sheet, I will keep the same data by removing duplicate entries.

As a result, on the 2nd sheet, I would like to see what all priorities an incident has gone through. For eg. If the incident has changed its priority from priority1 to priority2, the result should come as P1-P2, if the priority1 to priority2 to priority3 to priority4 the result should come as P1-p2-p3-p4.

For eg.

IMA212500- P3-P4

Hope I am clear. Thanks in advance

Regards
Ratish

 

[Peter_SSs]

Hope I am clear.

I'm afraid not clear enough. Your original sample data included dates and your description of what was required referred to that column.

check the final priority in column B based on Column C's updated Date and time

I asked for the expected results for that original data.

Instead, you have provided different data, with no date/time column and just one expected result. Please address each of the points below.

1. Could we see a set of sample data and the full expected results for that data, not a word description of the expected results?

2. Does the list of Image numbers (with removed duplicates) already exist in the second sheet, or are you requesting that to be created as part of the solution?

3. What are the 2 sheet names and identify which is which?

4. Would a macro solution be acceptable if that seems like the best way to go?

5. What version of Excel are you using?

 

[rkvalu]

Hi Peter,

Sorry for the confusion created. Please ignore above data. Below is refreshed data. So On my sheet 1 of excel sheet, I have below information

[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 4[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]Priority 3[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IMA282027[/TD]
[TD]Priority 1[/TD]
[/TR]
[TR]
[TD]IMA282027[/TD]
[TD]Priority 4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IMA282021[/TD]
[TD]Priority 2[/TD]
[/TR]
[TR]
[TD]IMA282021[/TD]
[TD]Priority 3[/TD]
[/TR]
</tbody>[/TABLE]

On Sheet 2 I have removed the duplicate values of above data and manually looked up the priority change. Below is the result

[TABLE="width: 181"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Priority Change[/TD]
[/TR]
[TR]
[TD]INC0201552[/TD]
[TD]No Change[/TD]
[/TR]
[TR]
[TD]INC0212500[/TD]
[TD]P3-P4[/TD]
[/TR]
[TR]
[TD]INC0222660[/TD]
[TD]P3-P4[/TD]
[/TR]
[TR]
[TD]INC0226274[/TD]
[TD]No Change[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 181"]
<tbody>[TR]
[TD="width: 78"]INC0282027[/TD]
[TD="width: 103"]P1-P4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 181"]
<tbody>[TR]
[TD="width: 78"]INC0282021[/TD]
[TD="width: 103"]P2-P3[/TD]
[/TR]
</tbody>[/TABLE]


4. Yes, Macro solution would be acceptable
5. Excel version is 2010

Hope I am clear.

 

[Peter_SSs]

On Sheet 2 I have removed the duplicate values of above data .... Below is the result

How is it that the first column in Sheet 1 are all "IMA... " values and when you have removed duplicates in Sheet 2 they are suddenly all "INC..." values?

 

[rkvalu]

Sorry My Bad.

[TABLE="class: cms_table, width: 181"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Priority Change[/TD]
[/TR]
[TR]
[TD]IMA201552[/TD]
[TD]No Change[/TD]
[/TR]
[TR]
[TD]IMA212500[/TD]
[TD]P3-P4[/TD]
[/TR]
[TR]
[TD]IMA222660[/TD]
[TD]P3-P4[/TD]
[/TR]
[TR]
[TD]IMA226274[/TD]
[TD]No Change[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 181"]
<tbody>[TR]
[TD="width: 78"]IMA282027[/TD]
[TD="width: 103"]P1-P4[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 181"]
<tbody>[TR]
[TD="width: 78"]IMA282021[/TD]
[TD="width: 103"]P2-P3[/TD]
[/TR]
</tbody>[/TABLE]

Regards
Ratish

How is it that the first column in Sheet 1 are all "IMA... " values and when you have removed duplicates in Sheet 2 they are suddenly all "INC..." values?