Frequent pattern in range of cells formula

[xlinator]

Hi Everyone,

I have 2 worksheets first sheet has list of computers. second sheet has list of computers (same computers as first sheet) but also has their errors , this sheet consists of 2 columns: computer name, error message. I have multiple error messages per computer, some repeating per computer. I need on the first sheet in a cell beside the computer to show the top most repeated error message per computer by that respective computer. I found online kind of what I want which is find the top repeated pattern in cell within a range but it doesn't depend on the computer, it just gives the top error in all the errors. also if there is nothing repeating, return "-"

I hope I explained the issue properly.

any help would be appreciated. I've been struggling with this for days.

 

[Aladin Akyurek]

Care to post here a 5 rows sample (input) along with the desired output?

 

[xlinator]

sure, thanks


sheet 1:
computer, top error
comp1 0x80070002 <-should return this
comp2 0x80244010 <-should return this

sheet2:
computer, error
COMP1 WARNING: Failed to delete service from the backup store, error = 0x80070002
COMP1 * WARNING: Failed to synchronize, error = 0x80244010
COMP1 WARNING: WU client failed Searching for update with error 0x80244010
COMP1 WARNING: WU client fails CClientCallRecorder::RemoveService with error 0x80248014
COMP1 WARNING: WU client fails CClientCallRecorder::RemoveService with error 0x80248014
COMP1 WARNING: Failed to delete service from the backup store, error = 0x80070002
COMP1 WARNING: failed to calculate prior restore point time with error 0x80070002; setting restore point
COMP2 WARNING: Failed to delete service from the backup store, error = 0x80070002
COMP2 * WARNING: Failed to synchronize, error = 0x80244010
COMP2 WARNING: WU client failed Searching for update with error 0x80244010
COMP2 WARNING: Operation failed due to earlier error, hr=80244010
COMP2 Windows Update Client failed to detect with error 0x80244010.
COMP2 WARNING: WU client fails CClientCallRecorder::RemoveService with error 0x80248014
COMP2 WARNING: WU client fails CClientCallRecorder::RemoveService with error 0x80248014
COMP2 WARNING: failed to calculate prior restore point time with error 0x80070002; setting restore point
COMP2 - Install call complete (succeeded = 1, succeeded with errors = 0, failed = 0, unaccounted = 0)
COMP2 Completed install of CBS update with type=3, requiresReboot=1, installerError=0, hr=0x0
COMP2 Completed install of CBS update with type=3, requiresReboot=1, installerError=0, hr=0x0

 

[xlinator]

I found this array formula: {=INDEX(I2:I2005,MATCH(MIN(COUNTIF(I2:I2005,I2:I2005)),COUNTIF(I2:I2005,I2:I2005),0))} that (presume I is the column for the errors) will find the top error in the list, but it ignores the computer column on both sheet 1 and 2. just don't understand enough about this to modify it for my needs.