IF/Then help

klynshoe

klynshoe

Board Regular
Joined
Jun 17, 2010
Messages
176
Hello,

I have a list of individuals with assignments that I want to create an if/then formula in another column.

If the employee has the S7 listed more than once, then only list the S7. If the employee has the S7 listed only once, then list all values. Otherwise list all values.


Sample data:
<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=463 border=0><COLGROUP><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 181pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=241 height=20>A</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=106>B</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=116>Desired Outcome</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abbadi, Karim</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S55</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abbadi, Karim</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abbadi, Karim</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abbadi, Karim</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abdo, Bernard</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abdo, Bernard</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abdo, Bernard</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abillama, Jean</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abillama, Jean</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abouelafia, Najah</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7,S79</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7,S79</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abrahams, Steven</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abrahams, Steven</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S24</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abrahams, Steven</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S16</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abrahams, Steven</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Abramson, Adam</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Abramson, Adam</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S7,S79</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">S7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abramson, Adam</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">S63</TD></TR></TBODY></TABLE>

You'll see that for Adam Abramson, the S79 is not listed because the S7 was listed twice for him. The others have the S79 listed (if applicable) because there is only one S7 listed. This is the desired outcome.

USING EXCEL 2007
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
This should work if the "S7" appears in the multi text cells first.

Excel Workbook
ABCD
5Abbadi, KarimS55S55S55
6Abbadi, KarimS7S7S7
7Abbadi, KarimS24S24S24
8Abbadi, KarimS63S63S63
9Abdo, BernardS7S7S7
10Abdo, BernardS24S24S24
11Abdo, BernardS63S63S63
12Abillama, JeanS7S7S7
13Abillama, JeanS63S63S63
14Abouelafia, NajahS7,S79S7,S79S7,S79
15Abrahams, StevenS7S7S7
16Abrahams, StevenS24S24S24
17Abrahams, StevenS16S16S16
18Abrahams, StevenS63S63S63
19Abramson, AdamS7S7S7
20Abramson, AdamS7,S79S7S79
21Abramson, AdamS63S63S63
Sheet2
Excel 2007
Cell Formulas
RangeFormula
D5=IF(SUMPRODUCT(--($A$5:$A$21=A5),--($B$5:$B$21>="s7"))<=1,B5,IF(LEN(B5)>5,RIGHT(B5,LEN(B5)-3),B5))
D6=IF(SUMPRODUCT(--($A$5:$A$21=A6),--($B$5:$B$21>="s7"))<=1,B6,IF(LEN(B6)>5,RIGHT(B6,LEN(B6)-3),B6))
D7=IF(SUMPRODUCT(--($A$5:$A$21=A7),--($B$5:$B$21>="s7"))<=1,B7,IF(LEN(B7)>5,RIGHT(B7,LEN(B7)-3),B7))
D8=IF(SUMPRODUCT(--($A$5:$A$21=A8),--($B$5:$B$21>="s7"))<=1,B8,IF(LEN(B8)>5,RIGHT(B8,LEN(B8)-3),B8))
D9=IF(SUMPRODUCT(--($A$5:$A$21=A9),--($B$5:$B$21>="s7"))<=1,B9,IF(LEN(B9)>5,RIGHT(B9,LEN(B9)-3),B9))
D10=IF(SUMPRODUCT(--($A$5:$A$21=A10),--($B$5:$B$21>="s7"))<=1,B10,IF(LEN(B10)>5,RIGHT(B10,LEN(B10)-3),B10))
D11=IF(SUMPRODUCT(--($A$5:$A$21=A11),--($B$5:$B$21>="s7"))<=1,B11,IF(LEN(B11)>5,RIGHT(B11,LEN(B11)-3),B11))
D12=IF(SUMPRODUCT(--($A$5:$A$21=A12),--($B$5:$B$21>="s7"))<=1,B12,IF(LEN(B12)>5,RIGHT(B12,LEN(B12)-3),B12))
D13=IF(SUMPRODUCT(--($A$5:$A$21=A13),--($B$5:$B$21>="s7"))<=1,B13,IF(LEN(B13)>5,RIGHT(B13,LEN(B13)-3),B13))
D14=IF(SUMPRODUCT(--($A$5:$A$21=A14),--($B$5:$B$21>="s7"))<=1,B14,IF(LEN(B14)>5,RIGHT(B14,LEN(B14)-3),B14))
D15=IF(SUMPRODUCT(--($A$5:$A$21=A15),--($B$5:$B$21>="s7"))<=1,B15,IF(LEN(B15)>5,RIGHT(B15,LEN(B15)-3),B15))
D16=IF(SUMPRODUCT(--($A$5:$A$21=A16),--($B$5:$B$21>="s7"))<=1,B16,IF(LEN(B16)>5,RIGHT(B16,LEN(B16)-3),B16))
D17=IF(SUMPRODUCT(--($A$5:$A$21=A17),--($B$5:$B$21>="s7"))<=1,B17,IF(LEN(B17)>5,RIGHT(B17,LEN(B17)-3),B17))
D18=IF(SUMPRODUCT(--($A$5:$A$21=A18),--($B$5:$B$21>="s7"))<=1,B18,IF(LEN(B18)>5,RIGHT(B18,LEN(B18)-3),B18))
D19=IF(SUMPRODUCT(--($A$5:$A$21=A19),--($B$5:$B$21>="s7"))<=1,B19,IF(LEN(B19)>5,RIGHT(B19,LEN(B19)-3),B19))
D20=IF(SUMPRODUCT(--($A$5:$A$21=A20),--($B$5:$B$21>="s7"))<=1,B20,IF(LEN(B20)>5,RIGHT(B20,LEN(B20)-3),B20))
D21=IF(SUMPRODUCT(--($A$5:$A$21=A21),--($B$5:$B$21>="s7"))<=1,B21,IF(LEN(B21)>5,RIGHT(B21,LEN(B21)-3),B21))
 
Upvote 0
This does not accomplish the desired outcome. Your formula lists the S79 if there is more than once instance of the S7, where it should list the S7. See row 20 in your result for Adam Abramson. It lists only the S79, where it should list only the S7.
 
Upvote 0
I must of read your initial post wrong. Can you answer me this: is s7 the only single digit number? Meaning would there every be S7 & S(single digit) together?
 
Upvote 0
Will this do for you?

Excel Workbook
ABCD
5Abbadi, KarimS55S55S55
6Abbadi, KarimS7S7S7
7Abbadi, KarimS24S24S24
8Abbadi, KarimS63S63S63
9Abdo, BernardS7S7S7
10Abdo, BernardS24S24S24
11Abdo, BernardS63S63S63
12Abillama, JeanS7S7S7
13Abillama, JeanS63S63S63
14Abouelafia, NajahS7,S79S7,S79S7,S79
15Abrahams, StevenS7S7S7
16Abrahams, StevenS24S24S24
17Abrahams, StevenS16S16S16
18Abrahams, StevenS63S63S63
19Abramson, AdamS7S7S7
20Abramson, AdamS7,S79S7S7
21Abramson, AdamS63S63S63
Sheet2
Excel 2007
Cell Formulas
RangeFormula
D5=IF(SUMPRODUCT(--($A$5:$A$21=A5),--($B$5:$B$21>="s7"))<=1,B5,IF(LEN(B5)>5,LEFT(B5,2),B5))
D6=IF(SUMPRODUCT(--($A$5:$A$21=A6),--($B$5:$B$21>="s7"))<=1,B6,IF(LEN(B6)>5,LEFT(B6,2),B6))
D7=IF(SUMPRODUCT(--($A$5:$A$21=A7),--($B$5:$B$21>="s7"))<=1,B7,IF(LEN(B7)>5,LEFT(B7,2),B7))
D8=IF(SUMPRODUCT(--($A$5:$A$21=A8),--($B$5:$B$21>="s7"))<=1,B8,IF(LEN(B8)>5,LEFT(B8,2),B8))
D9=IF(SUMPRODUCT(--($A$5:$A$21=A9),--($B$5:$B$21>="s7"))<=1,B9,IF(LEN(B9)>5,LEFT(B9,2),B9))
D10=IF(SUMPRODUCT(--($A$5:$A$21=A10),--($B$5:$B$21>="s7"))<=1,B10,IF(LEN(B10)>5,LEFT(B10,2),B10))
D11=IF(SUMPRODUCT(--($A$5:$A$21=A11),--($B$5:$B$21>="s7"))<=1,B11,IF(LEN(B11)>5,LEFT(B11,2),B11))
D12=IF(SUMPRODUCT(--($A$5:$A$21=A12),--($B$5:$B$21>="s7"))<=1,B12,IF(LEN(B12)>5,LEFT(B12,2),B12))
D13=IF(SUMPRODUCT(--($A$5:$A$21=A13),--($B$5:$B$21>="s7"))<=1,B13,IF(LEN(B13)>5,LEFT(B13,2),B13))
D14=IF(SUMPRODUCT(--($A$5:$A$21=A14),--($B$5:$B$21>="s7"))<=1,B14,IF(LEN(B14)>5,LEFT(B14,2),B14))
D15=IF(SUMPRODUCT(--($A$5:$A$21=A15),--($B$5:$B$21>="s7"))<=1,B15,IF(LEN(B15)>5,LEFT(B15,2),B15))
D16=IF(SUMPRODUCT(--($A$5:$A$21=A16),--($B$5:$B$21>="s7"))<=1,B16,IF(LEN(B16)>5,LEFT(B16,2),B16))
D17=IF(SUMPRODUCT(--($A$5:$A$21=A17),--($B$5:$B$21>="s7"))<=1,B17,IF(LEN(B17)>5,LEFT(B17,2),B17))
D18=IF(SUMPRODUCT(--($A$5:$A$21=A18),--($B$5:$B$21>="s7"))<=1,B18,IF(LEN(B18)>5,LEFT(B18,2),B18))
D19=IF(SUMPRODUCT(--($A$5:$A$21=A19),--($B$5:$B$21>="s7"))<=1,B19,IF(LEN(B19)>5,LEFT(B19,2),B19))
D20=IF(SUMPRODUCT(--($A$5:$A$21=A20),--($B$5:$B$21>="s7"))<=1,B20,IF(LEN(B20)>5,LEFT(B20,2),B20))
D21=IF(SUMPRODUCT(--($A$5:$A$21=A21),--($B$5:$B$21>="s7"))<=1,B21,IF(LEN(B21)>5,LEFT(B21,2),B21))
 
Upvote 0
I'm not sure about your first question but this new formula appears to work the way intended. Thanks so much for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
175
jolivanes
jolivanes
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,521
Messages
6,179,284
Members
452,902
Latest member
Knuddeluff

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