Concatenate and Match Data??

P

Paul36

New Member
Joined
Jul 9, 2010
Messages
12
I have the following data:

<TABLE style="WIDTH: 454pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=604><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" span=2 width=74><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17 width=61></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=64>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=64>B</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=64>C</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=75>D</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=74>E</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=74>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=64>G</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 width=64>H</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82>Machine</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Shift</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Activity</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>First Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Last Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Drill</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>B</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Drill</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Mike</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Miller</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>4</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Night</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Condition</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>John</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Davidson</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>B</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Night</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Condition</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Harry</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Benson</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>6</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>David</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Turner</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>7</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Condition</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>8</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>B</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Standby</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Mike</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Miller</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl89 height=17>9</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>B</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>21-Oct-10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Day</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>Blast</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88></TD></TR></TBODY></TABLE>


What I would like to do is to have a formula that puts in the employee's name in each row in Column G and H. In the above data, you can see in Row 2, there is no employee name for Machine A that drilled on day shift on 21 Oct. However, in Row 6 where the activity is blank, there is employee name (David Turner) showing against Machine A on day shift on 21 October.

So I need excel to concatenate Columns A, B and C together, and then if that row has an existing employee name (eg Row 3 above), return the name in that row in Column G and H. If there is no name (eg Row 2 above), find any row that has an employee's name in it for the same machine, date and shift (eg Row 6), and then return that name in Column G and H.

My data is thousands of rows long, and each machine will have multiple activities for the same date and shift. Some of the activities will have names in Columns E and F, but most will not.

This one is too complicated for my little brain, so any help is greatly appreciated. Please let me know if I have not explained the problem clearly.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi

How about
Excel Workbook
ABCDEFGH
1MachineDateShiftActivityFirst NameLast NameFirst NameLast Name
2A21-Oct-10DayDrillDavidTurner
3B21-Oct-10DayDrillMikeMillerMikeMiller
4A21-Oct-10NightConditionJohnDavidsonJohnDavidson
5B21-Oct-10NightConditionHarryBensonHarryBenson
6A21-Oct-10DayDavidTurnerDavidTurner
7A21-Oct-10DayCondition#N/A#N/A
8B21-Oct-10DayStandbyMikeMillerMikeMiller
9B21-Oct-10DayBlast#N/A#N/A
Sheet1
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
Thanks a lot for your reply Sandeep, but how about Rows 7 and 9? They should also have names in them too...Row 7 = David Turner and Row 9 = Mike Miller?
 
Upvote 0
Hi Paul,

Apply the formula to your worksheet and extend the ranges to match your actual ranges.

1 question:

The sample data contains 2 blank names for A, 21 Oct and Day.... with only one name (david) that satisfies the condition... is it allright for the name to be repeated again?

If yes then change the formula to...

G2=IF(E2<>"",E2,INDEX(E$2:E$9,MATCH(1,($A$2:$A$9&"@"&$B$2:$B$9&"@"&$C$2:$C$9=$A2&"@"&$B2&"@"&$C2)*($D$2:$D$9=""),0)))

H2=IF(F2<>"",F2,INDEX(F$2:F$9,MATCH(1,($A$2:$A$9&"@"&$B$2:$B$9&"@"&$C$2:$C$9=$A2&"@"&$B2&"@"&$C2)*($D$2:$D$9=""),0)))
 
Upvote 0
Yes the name can be repeated, in fact that is what I want it to do. So the combination of Machine, date and shift should always have the same name against each activity.

I tried your formula Sandeep, and it works if there is already a name in the column, but for all the entries where there is no name, it is returning an error #NA.

If you don't mind, maybe you could do a formula to concatenate the machine, date and shift first and put that in a separate column, and then do a formula in another column to match it. That way might be a bit easier for me to follow :).

Thanks a lot for your prompt answers too!
 
Upvote 0
Hi Paul,

Does this help?
Excel Workbook
ABCDEFGHIJ
1MachineDateShiftActivityFirst NameLast NameFirst NameLast NameConcatenated Field1Concatenated Field2
2A21-Oct-10DayDrillDavidTurnerA@40472@DayA@40472@DayFALSE
3B21-Oct-10DayDrillMikeMillerMikeMillerB@40472@DayB@40472@DayFALSE
4A21-Oct-10NightConditionJohnDavidsonJohnDavidsonA@40472@NightA@40472@NightFALSE
5B21-Oct-10NightConditionHarryBensonHarryBensonB@40472@NightB@40472@NightFALSE
6A21-Oct-10DayDavidTurnerDavidTurnerA@40472@DayA@40472@DayTRUE
7A21-Oct-10DayConditionDavidTurnerA@40472@DayA@40472@DayFALSE
8B21-Oct-10DayStandbyMikeMillerMikeMillerB@40472@DayB@40472@DayFALSE
9B21-Oct-10DayBlastName Not FoundName Not FoundB@40472@DayB@40472@DayFALSE
Sheet1
Excel 2003
Cell Formulas
RangeFormula
G2=IF(E2<>"",E2,IF(ISNUMBER(MATCH(I2&"TRUE",$J$2:$J$9,0)),INDEX(E$2:E$9,MATCH(I2&"TRUE",$J$2:$J$9,0)),"Name Not Found"))
H2=IF(F2<>"",F2,IF(ISNUMBER(MATCH(I2&"TRUE",$J$2:$J$9,0)),INDEX(F$2:F$9,MATCH(I2&"TRUE",$J$2:$J$9,0)),"Name Not Found"))
I2=A2&"@"&B2&"@"&C2
J2=I2&(D2="")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
834
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
514
John_w
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,053
Messages
6,122,888
Members
449,097
Latest member
dbomb1414

Share this page

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