Return a value if a condition is met

[manojrf]

Hi friends,


I have been trying to figure out the solution to find a formula to return a value if a letter or word is repeated 2 times in range.


For example :


Sheet1

	A	B	C	D	E	F
S	B	N	U	I	S
					H

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4






As shown above, the letter "S" is repeated 2 times in Row 1. What I am trying to do is to make a formula to put "H" in Row 2 below the second "S" (found in F1).


I have tried countif, count, rept and so on. But couldn't make one.


Can anyone please help me?

Hope someone would help me.


Thanks in advance.

 

[TryingSomethingNew]

I assume you're putting the formula in all cells across row 2.
If you use

=IF(COUNTIF($A$1:A$1,A1)>1,"H","")

That should work.

Sheet1

	A	B	C	D	E	F
S	B	N	U	I	S
					H

<tbody>
[TD="align: center"]1[/TD]

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

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4






As shown above, the letter "S" is repeated 2 times in Row 1. What I am trying to do is to make a formula to put "H" in Row 2 below the second "S" (found in F1).


I have tried countif, count, rept and so on. But couldn't make one.


Can anyone please help me?

Hope someone would help me.


Thanks in advance.[/QUOTE]

 

[manojrf]

Hi,

Forgot to tell that the letters in Row 1 may get scrambled and so the position of the letter S may change.

Actually I am trying to put the letter H in Row 2, below the second and fourth S (which is Row 1)

For eg:

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]S[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]H[/TD]

[TD="align: center"]H[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Excel tables to the web >> Excel Jeanie HTML 4

Thanks in advance.

 

[CyrusTheVirus]

The above formula from TryingSomethingNew should work

 

[manojrf]

Thanks TrySomethingNew & CyrusTheVirus. Thanks for the reply.

From the hint TrySomethingNew gave me, I have found out the formula I was looking for.

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]S[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]H[/TD]

[TD="align: center"]H[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =IF(COUNTIF($A$1:A$1,A1)=2,"H",IF(COUNTIF($A$1:A$1,A1)=4,"H","")) B2 =IF(COUNTIF($A$1:B$1,B1)=2,"H",IF(COUNTIF($A$1:B$1,B1)=4,"H","")) C2 =IF(COUNTIF($A$1:C$1,C1)=2,"H",IF(COUNTIF($A$1:C$1,C1)=4,"H","")) D2 =IF(COUNTIF($A$1:D$1,D1)=2,"H",IF(COUNTIF($A$1:D$1,D1)=4,"H","")) E2 =IF(COUNTIF($A$1:E$1,E1)=2,"H",IF(COUNTIF($A$1:E$1,E1)=4,"H","")) F2 =IF(COUNTIF($A$1:F$1,F1)=2,"H",IF(COUNTIF($A$1:F$1,F1)=4,"H","")) G2 =IF(COUNTIF($A$1:G$1,G1)=2,"H",IF(COUNTIF($A$1:G$1,G1)=4,"H","")) H2 =IF(COUNTIF($A$1:H$1,H1)=2,"H",IF(COUNTIF($A$1:H$1,H1)=4,"H","")) I2 =IF(COUNTIF($A$1:I$1,I1)=2,"H",IF(COUNTIF($A$1:I$1,I1)=4,"H","")) J2 =IF(COUNTIF($A$1:J$1,J1)=2,"H",IF(COUNTIF($A$1:J$1,J1)=4,"H","")) K2 =IF(COUNTIF($A$1:K$1,K1)=2,"H",IF(COUNTIF($A$1:K$1,K1)=4,"H","")) L2 =IF(COUNTIF($A$1:L$1,L1)=2,"H",IF(COUNTIF($A$1:L$1,L1)=4,"H","")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I appreciate the time you have spent for me.