How to find position of certain value within group

[sadonnie]

Not sure my title is aligned with what I am trying to accomplish. So apologies. I have a table with a column Source HostName (Column G) and for each Source HostName there could 1 to many attempts in migrating that Host. When migrating that host, we either have a 'Success' or 'Error' Status (Column F). I need to find when the Source Host migrated resulted in a Success. Obviously, it is easy when there is one record since it was successful on first attempt. Or opposite, when it has all errors there is no Success.

However, when the first (or 2nd or 3rd) attempt(s) might be an error, I am trying to come up with a formula or vba code to find out that (for example) for source Hostname = Server1 the Status = Success happened in the 4th attempt. Hope that makes sense.

I do sort by the Source Hostname and then by Start Time which is coming out of a server in it's given format.

Here is a sample table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]UUID[/TD]
[TD]StartTime[/TD]
[TD]OSType[/TD]
[TD]TargetHost[/TD]
[TD]MigrationType[/TD]
[TD]Status[/TD]
[TD]SourceHost[/TD]
[/TR]
[TR]
[TD]24b06954-44b3-4ddc-b7f6-03c6672c1bfe[/TD]
[TD]2018-08-08T09:17:08.000Z[/TD]
[TD]Windows[/TD]
[TD]BackupProxy[/TD]
[TD]Full[/TD]
[TD]error[/TD]
[TD][TABLE="width: 142"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]b071c7cb-a70a-49b8-8758-17bfb98a3bbd[/TD]
[TD]2018-08-14T08:33:43.000Z[/TD]
[TD]Windows[/TD]
[TD]Subodh-Failback-subodh_backup_proxy_RFS[/TD]
[TD]Full[/TD]
[TD]success[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD]4f24466d-2465-48ee-8cc4-0c8b33cd103e[/TD]
[TD]2018-08-14T14:43:13.000Z[/TD]
[TD]Windows[/TD]
[TD]Subodh-Failback-subodh_backup_proxy_RFS[/TD]
[TD]Full[/TD]
[TD]error[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD]ae57a1b9-bb7d-45b6-9d80-c8108f1c4ff3[/TD]
[TD]2018-08-20T07:32:24.000Z[/TD]
[TD]Windows[/TD]
[TD]Subodh_Failover_RM_Subodh_backup_proxy_4_RFS[/TD]
[TD]Full[/TD]
[TD]success[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD]03c0db8c-b462-4fd7-948d-0ebbe0ad11e6[/TD]
[TD]2018-09-10T21:10:51.000Z[/TD]
[TD]Linux[/TD]
[TD]Barge1[/TD]
[TD]Full[/TD]
[TD]error[/TD]
[TD]Barge1[/TD]
[/TR]
[TR]
[TD]5b3892bd-ac9d-41d8-a96a-72029c02c29b[/TD]
[TD]2018-09-11T14:49:30.000Z[/TD]
[TD]Linux[/TD]
[TD]Barge1_Dev[/TD]
[TD]Full[/TD]
[TD]success[/TD]
[TD]Barge1[/TD]
[/TR]
[TR]
[TD]8023a55f-67d3-47fe-9103-8c063c3cb2d0[/TD]
[TD]2018-09-12T11:50:30.000Z[/TD]
[TD]Linux[/TD]
[TD]Barge1[/TD]
[TD]Full[/TD]
[TD]error[/TD]
[TD]Barge1[/TD]
[/TR]
</tbody>[/TABLE]


So for example, I am trying to get a result for Source Hostname = Barge1 where 'success' happened in the 2nd attempt. And while Source Hostname = BackupProxy had more than 1 'success' I want to find out that it happened on the 2nd attempt even though there was a success on the 4th attempt as well. I want the first occurrence.

Thanks for anyone who can assist with some recommendations.

 

[Eric W]

Welcome to the MrExcel board.

Try this:

	A	B	C	D	E	F	G	H	I	J	K	L
Windows	2018-09-11T14:49:30.000Z

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]UUID[/TD]
[TD="bgcolor: #FAFAFA"]StartTime[/TD]
[TD="bgcolor: #FAFAFA"]OSType[/TD]
[TD="bgcolor: #FAFAFA"]TargetHost[/TD]
[TD="bgcolor: #FAFAFA"]MigrationType[/TD]
[TD="bgcolor: #FAFAFA"]Status[/TD]
[TD="bgcolor: #FAFAFA"]SourceHost[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]SourceHost[/TD]
[TD="bgcolor: #FAFAFA"]First Success Time[/TD]
[TD="align: right"]Attempt #[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]24b06954-44b3-4ddc-b7f6-03c6672c1bfe[/TD]
[TD="bgcolor: #FAFAFA"]2018-08-08T09:17:08.000Z[/TD]

[TD="bgcolor: #FAFAFA"]BackupProxy[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]error[/TD]
[TD="bgcolor: #FAFAFA"]BackupProxy[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]b071c7cb-a70a-49b8-8758-17bfb98a3bbd[/TD]
[TD="bgcolor: #FAFAFA"]2018-08-14T08:33:43.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Windows[/TD]
[TD="bgcolor: #FAFAFA"]Subodh-Failback-subodh_backup_proxy_RFS[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]success[/TD]
[TD="bgcolor: #FAFAFA"]BackupProxy[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]4f24466d-2465-48ee-8cc4-0c8b33cd103e[/TD]
[TD="bgcolor: #FAFAFA"]2018-08-14T14:43:13.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Windows[/TD]
[TD="bgcolor: #FAFAFA"]Subodh-Failback-subodh_backup_proxy_RFS[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]error[/TD]
[TD="bgcolor: #FAFAFA"]BackupProxy[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]ae57a1b9-bb7d-45b6-9d80-c8108f1c4ff3[/TD]
[TD="bgcolor: #FAFAFA"]2018-08-20T07:32:24.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Windows[/TD]
[TD="bgcolor: #FAFAFA"]Subodh_Failover_RM_Subodh_backup_proxy_4_RFS[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]success[/TD]
[TD="bgcolor: #FAFAFA"]BackupProxy[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]03c0db8c-b462-4fd7-948d-0ebbe0ad11e6[/TD]
[TD="bgcolor: #FAFAFA"]2018-09-10T21:10:51.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Linux[/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]error[/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]5b3892bd-ac9d-41d8-a96a-72029c02c29b[/TD]
[TD="bgcolor: #FAFAFA"]2018-09-11T14:49:30.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Linux[/TD]
[TD="bgcolor: #FAFAFA"]Barge1_Dev[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]success[/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]8023a55f-67d3-47fe-9103-8c063c3cb2d0[/TD]
[TD="bgcolor: #FAFAFA"]2018-09-12T11:50:30.000Z[/TD]
[TD="bgcolor: #FAFAFA"]Linux[/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]
[TD="bgcolor: #FAFAFA"]Full[/TD]
[TD="bgcolor: #FAFAFA"]error[/TD]
[TD="bgcolor: #FAFAFA"]Barge1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=INDEX(B2:B8,MATCH("success|"&I2,F2:F8&"|"&G2:G8,0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]{=COUNTIF(OFFSET(G2,0,0,MATCH("success|"&I2,F2:F8&"|"&G2:G8,0)),I2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with C
trl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[sadonnie]

Thanks Eric. Appreciate your recommendation. I did follow your lead and it worked. But do have a quick follow up question. Since I have a table with a lot of unique source hostnames (say 60 +/-) can I somehow drag the array formula down a distinct list of hostnames so I do not have to create 60+/- hostnames? I tried and it worked and didn't work if I used an absolute reference for the B:B and G:G and F:F


Example, BackProxy is saying that it found 'success' on 3rd attempt but it was really the 1st. See master table data below.

[TABLE]
<colgroup><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]2[/TD]
[TD]AUPRTSAP01[/TD]
[TD]2018-09-28T21:31:53.000Z[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AUTOCHECK4-VM[/TD]
[TD]2018-07-07T15:31:12.000Z[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]BackupProxy[/TD]
[TD]2018-08-08T09:17:08.000Z[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Bamboo1-VM[/TD]
[TD]2018-07-06T03:23:12.000Z[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Barge1[/TD]
[TD]2018-09-11T14:49:30.000Z[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet3[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Array Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD]{=INDEX([COLOR=Blue]B$2:B$20,MATCH([COLOR=Red]"success|"&I2,F$2:F$20&"|"&G$2:G$20,0[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD]{=COUNTIF([COLOR=Blue]OFFSET([COLOR=Red]G2,0,0,MATCH([COLOR=Green]"success|"&I2,F$2:F20&"|"&G$2:G$20,0[/COLOR])[/COLOR]),I2[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD]{=INDEX([COLOR=Blue]B$2:B$20,MATCH([COLOR=Red]"success|"&I3,F$2:F$20&"|"&G$2:G$20,0[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD]{=COUNTIF([COLOR=Blue]OFFSET([COLOR=Red]G3,0,0,MATCH([COLOR=Green]"success|"&I3,F$2:F21&"|"&G$2:G$20,0[/COLOR])[/COLOR]),I3[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD]{=INDEX([COLOR=Blue]B$2:B$20,MATCH([COLOR=Red]"success|"&I4,F$2:F$20&"|"&G$2:G$20,0[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K4[/TH]
[TD]{=COUNTIF([COLOR=Blue]OFFSET([COLOR=Red]G4,0,0,MATCH([COLOR=Green]"success|"&I4,F$2:F20&"|"&G$2:G$20,0[/COLOR])[/COLOR]),I4[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD]{=INDEX([COLOR=Blue]B$2:B$20,MATCH([COLOR=Red]"success|"&I5,F$2:F$20&"|"&G$2:G$20,0[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K5[/TH]
[TD]{=COUNTIF([COLOR=Blue]OFFSET([COLOR=Red]G5,0,0,MATCH([COLOR=Green]"success|"&I5,F$2:F23&"|"&G$2:G$20,0[/COLOR])[/COLOR]),I5[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD]{=INDEX([COLOR=Blue]B$2:B$20,MATCH([COLOR=Red]"success|"&I6,F$2:F$20&"|"&G$2:G$20,0[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K6[/TH]
[TD]{=COUNTIF([COLOR=Blue]OFFSET([COLOR=Red]G6,0,0,MATCH([COLOR=Green]"success|"&I6,F$2:F24&"|"&G$2:G$20,0[/COLOR])[/COLOR]),I6[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE]
<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[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="align: center"]1[/TD]
[TD="align: center"]UUID[/TD]
[TD="align: center"]StartTime[/TD]
[TD="align: center"]OSType[/TD]
[TD="align: center"]TargetHost[/TD]
[TD="align: center"]MigrationType[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]SourceHost[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]24b06954-44b3-4ddc-b7f6-03c6672c1bfe[/TD]
[TD]2018-09-24T21:23:53.000Z[/TD]
[TD]windows[/TD]
[TD]APS2WSAP01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTSAP01[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]d7300d83-f2be-4677-9dd1-84b30f1b1282[/TD]
[TD]2018-09-27T21:12:18.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTSAP01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTSAP01[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]063a40f0-156c-4268-8a07-f9e3933ffb53[/TD]
[TD]2018-09-28T19:26:18.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTSAP01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTSAP01[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]b071c7cb-a70a-49b8-8758-17bfb98a3bbd[/TD]
[TD]2018-09-28T21:23:08.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTSAP01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTSAP01[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]82b6dc19-d2d7-41f0-8674-4ae042d21c49[/TD]
[TD]2018-09-28T21:31:53.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTSAP01[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]AUPRTSAP01[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]70656df6-fee9-4137-90a9-dda8e2780a96[/TD]
[TD]2018-09-24T17:26:03.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTTSR01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTTSR01[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]4f24466d-2465-48ee-8cc4-0c8b33cd103e[/TD]
[TD]2018-09-25T04:35:56.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTTSR01-Test[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTTSR01[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]42db33b4-14d3-4643-a526-104f85f6a48b[/TD]
[TD]2018-09-25T21:35:53.000Z[/TD]
[TD]windows[/TD]
[TD]AUPRTTSR01[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTTSR01[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]9f03c5cf-b832-4447-8d6f-7762d88455bd[/TD]
[TD]2018-09-26T11:47:56.000Z[/TD]
[TD]windows[/TD]
[TD]APS2TEST-TSR1[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]AUPRTTSR01[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]caa9bf81-df6f-4efc-9832-ad47d09a2785[/TD]
[TD]2018-09-26T21:29:42.000Z[/TD]
[TD]windows[/TD]
[TD]APS2WTSF01[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]AUPRTTSR01[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]ccd90570-8683-4917-9701-e3ee6e3f3b13[/TD]
[TD]2018-07-07T15:31:12.000Z[/TD]
[TD]windows[/TD]
[TD]AUTOCHECK4-VM[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]AUTOCHECK4-VM[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]c143a2f1-d374-4ed7-a906-6b2feeadca2d[/TD]
[TD]2018-07-09T01:44:14.000Z[/TD]
[TD]windows[/TD]
[TD]AUTOCHECK4-VM[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]AUTOCHECK4-VM[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]f89466ba-45ee-4c0e-a1bb-9777efb600f9[/TD]
[TD]2018-08-08T09:17:08.000Z[/TD]
[TD]linux[/TD]
[TD]BackupProxy[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]a9a2f301-f7ce-4577-9268-d2454b7b60b4[/TD]
[TD]2018-08-14T08:33:43.000Z[/TD]
[TD]linux[/TD]
[TD]Subodh-Failback-subodh_backup_proxy_RFS[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]ae57a1b9-bb7d-45b6-9d80-c8108f1c4ff3[/TD]
[TD]2018-08-14T14:43:13.000Z[/TD]
[TD]linux[/TD]
[TD]Subodh_Failback_RM_Subodh_backup_proxy_RFS[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]0c5069df-af3c-410d-a960-2a9c83bcb403[/TD]
[TD]2018-08-20T07:32:24.000Z[/TD]
[TD]linux[/TD]
[TD]Subodh_Failover_RM_Subodh_backup_proxy_4_RFS[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]BackupProxy[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]03c0db8c-b462-4fd7-948d-0ebbe0ad11e6[/TD]
[TD]2018-07-06T03:23:12.000Z[/TD]
[TD]windows[/TD]
[TD]940411-SYD2-BMBOO-PRD1.com.au[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]Bamboo1-VM[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]5b3892bd-ac9d-41d8-a96a-72029c02c29b[/TD]
[TD]2018-09-10T21:10:51.000Z[/TD]
[TD]windows[/TD]
[TD]Barge1[/TD]
[TD]full[/TD]
[TD]error[/TD]
[TD]Barge1[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]8023a55f-67d3-47fe-9103-8c063c3cb2d0[/TD]
[TD]2018-09-11T14:49:30.000Z[/TD]
[TD]windows[/TD]
[TD]Barge1[/TD]
[TD]full[/TD]
[TD]success[/TD]
[TD]Barge1[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet3[/B][/COLOR][/CENTER]

 

[Eric W]

The problem with the K2 formula is, as you surmised, that I didn't use the absolute references for some ranges. Sorry about that, I didn't anticipate you wanting to drag it down. It should be:

=COUNTIF(OFFSET($G$2,0,0,MATCH("success|"&I2,$F$2:$F$20&"|"&$G$2:$G$20,0)),I2)

As far as creating a list of unique Sourcehost names, the easiest way would be to just copy column G to column I, then use the Remove Duplicates tool on the Data tab. If you want a dynamic function, you could use this formula in I2 and drag down:

=INDEX($G$2:$G$20,MATCH(0,COUNTIF($G$2:$G$20,"<"&$G$2:$G$20)-SUM(COUNTIF($G$2:$G$20,$I$1:$I1)),0))
with CSE.

Hope this works a little better!