How to find position of certain value within group

sadonnie

New Member
Joined
Dec 17, 2018
Messages
13
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

UUIDStartTimeOSTypeTargetHostMigrationTypeStatusSourceHost
24b06954-44b3-4ddc-b7f6-03c6672c1bfe2018-08-08T09:17:08.000ZWindowsBackupProxyFullerror
BackupProxy

<colgroup><col></colgroup><tbody>
</tbody>
b071c7cb-a70a-49b8-8758-17bfb98a3bbd2018-08-14T08:33:43.000ZWindowsSubodh-Failback-subodh_backup_proxy_RFSFullsuccessBackupProxy
4f24466d-2465-48ee-8cc4-0c8b33cd103e2018-08-14T14:43:13.000ZWindowsSubodh-Failback-subodh_backup_proxy_RFSFullerrorBackupProxy
ae57a1b9-bb7d-45b6-9d80-c8108f1c4ff32018-08-20T07:32:24.000ZWindowsSubodh_Failover_RM_Subodh_backup_proxy_4_RFSFullsuccessBackupProxy
03c0db8c-b462-4fd7-948d-0ebbe0ad11e62018-09-10T21:10:51.000ZLinuxBarge1FullerrorBarge1
5b3892bd-ac9d-41d8-a96a-72029c02c29b2018-09-11T14:49:30.000ZLinuxBarge1_DevFullsuccessBarge1
8023a55f-67d3-47fe-9103-8c063c3cb2d02018-09-12T11:50:30.000ZLinuxBarge1FullerrorBarge1

<tbody>
</tbody>


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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Welcome to the MrExcel board.

Try this:

ABCDEFGHIJKL
1UUIDStartTimeOSTypeTargetHostMigrationTypeStatusSourceHostSourceHostFirst Success TimeAttempt #
224b06954-44b3-4ddc-b7f6-03c6672c1bfe2018-08-08T09:17:08.000ZWindowsBackupProxyFullerrorBackupProxyBarge12018-09-11T14:49:30.000Z2
3b071c7cb-a70a-49b8-8758-17bfb98a3bbd2018-08-14T08:33:43.000ZWindowsSubodh-Failback-subodh_backup_proxy_RFSFullsuccessBackupProxy
44f24466d-2465-48ee-8cc4-0c8b33cd103e2018-08-14T14:43:13.000ZWindowsSubodh-Failback-subodh_backup_proxy_RFSFullerrorBackupProxy
5ae57a1b9-bb7d-45b6-9d80-c8108f1c4ff32018-08-20T07:32:24.000ZWindowsSubodh_Failover_RM_Subodh_backup_proxy_4_RFSFullsuccessBackupProxy
603c0db8c-b462-4fd7-948d-0ebbe0ad11e62018-09-10T21:10:51.000ZLinuxBarge1FullerrorBarge1
75b3892bd-ac9d-41d8-a96a-72029c02c29b2018-09-11T14:49:30.000ZLinuxBarge1_DevFullsuccessBarge1
88023a55f-67d3-47fe-9103-8c063c3cb2d02018-09-12T11:50:30.000ZLinuxBarge1FullerrorBarge1

<tbody>
</tbody>
Sheet16

Array Formulas
CellFormula
J2{=INDEX(B2:B8,MATCH("success|"&I2,F2:F8&"|"&G2:G8,0))}
K2{=COUNTIF(OFFSET(G2,0,0,MATCH("success|"&I2,F2:F8&"|"&G2:G8,0)),I2)}

<tbody>
</tbody>
Entered with C
trl+Shift+Enter.
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
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]

 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top