Help needed with a formula that Skip the predefined Value and Ignore the duplicate contents in a row

[knis80]

Hi,
I am creating a spreadsheet with several values (one Text value in each cell) i.e.

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]Row1[/TD]
[TD]Col. A[/TD]
[TD]Col. B[/TD]
[TD]Col. C[/TD]
[TD]Col. D[/TD]
[TD]Col. E (The Result)[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]NA[/TD]
[TD]Pumpkin[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]Cherry[/TD]
[TD]Apple[/TD]
[TD]Mango[/TD]
[TD]Pineapple[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]NA[/TD]
[TD]Pumpkin[/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]










I wish to get the result in Column F as follows:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Row1[/TD]
[TD]Column F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]Apple Banana Pumpkin[/TD]
[TD](Without the text “NA”)[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]Cherry Apple Mango Pineapple[/TD]
[TD](Without the text “NA”)[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]Apple Banana Pumpkin[/TD]
[TD](Without the text “NA” and Ignoring the Double Value “Apple”)[/TD]
[/TR]
</tbody>[/TABLE]















I Tried Using following command but it displays all the five values presented in each cell.

=A2&" "&B2&" "&C2&" "&D2&" "&E2

Is there any way by which I can Ignore the specified word “NA” which may be presented in any of the cell
(A2 B2 C2 D2 E2) along with the duplicate value “Apple” (that may be presented in any of the rows) Ignored?


Please Help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function knis80(Rng As Range) As String
   Dim Cl As Range
   With CreateObject("scripting.dictionary")
      For Each Cl In Rng
         If Cl.Value <> "NA" And Cl.Value <> "" Then
            .item(Cl.Value) = Empty
         End If
      Next Cl
      knis80 = Join(.keys, Chr(10))
   End With
End Function

Used like

Book1
	A	B	C	D	E	G
1	Col. A	Col. B	Col. C	Col. D	Col. E (The Result)
2	Apple	Banana	NA	Pumpkin	NA	Apple Banana Pumpkin
3	Cherry	Apple	Mango	NA	Banana	Cherry Apple Mango Banana
4	Apple	Banana	NA	Pumpkin	Apple	Apple Banana Pumpkin
Delete

Cell Formulas
Range	Formula
G2	=knis80(A2:E2)

 

[knis80]

Thank you very much Fluff, your reply is like the water in desert for me, I've applied the function provided and got the result perfectly as what was desired by me, I suppose there must be a minor issue occurred while applying the code by me because I am getting the result as follows:




"AppleBananaPumpkin"
"CherryfluffMangoDAVE"
"AppleBananaPumpkinDAVE"


Please also tell me what I shall to do to put spaces between each word.


Thanks & Regards

 

[Fluff]

If you format the cells for Wrap text, then you will see each word on a new line.
Alternatively you can use

Function knis80(Rng As Range, Optional Delim As String = " ") As String
   Dim Cl As Range
   With CreateObject("scripting.dictionary")
      For Each Cl In Rng
         If Cl.Value <> "NA" And Cl.Value <> "" Then
            .item(Cl.Value) = Empty
         End If
      Next Cl
      knis80 = Join(.keys, Delim)
   End With
End Function

Book1
	A	B	C	D	E	F
1	Col. A	Col. B	Col. C	Col. D	Col. E (The Result)
2	Apple	Banana	NA	Pumpkin	NA	Apple, Banana, Pumpkin
3	Cherry	Apple	Mango	NA	Banana	Cherry - Apple - Mango - Banana
4	Apple	Mango	NA	Pumpkin	Apple	Apple Mango Pumpkin
Delete

Cell Formulas
Range	Formula
F2	=knis80(A2:E2,", ")
F3	=knis80(A3:E3," - ")
F4	=knis80(A4:E4,CHAR(10))

 

[knis80]

In Addition to this Please guide me! "is there any possibilities to remove duplicate words from a cell that contains several words? i.e. if a if i merge Column A, B, C, D, E results in Column F using the following command =A1&" "&B1&" "&C1&" "&D1&" "&E1 and merge Column G, H,I, J, K results in Column L.

so can I use this function to remove duplicate words and "NA" from column F & L using this function =knis80(F2:L2) I tried Using this function in following manner =knis80(F3,L3,Q3,U3) but got #VALUE as result.

hoping for your reply soon,
Regards

 

[Fluff]

To do that you would need

Function knis80(Rng1 As Range, Rng2 As Range, Optional Delim As String = " ") As String
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   With CreateObject("scripting.dictionary")
      For Each Cl In Union(Rng1, Rng2)
         Sp = Split(Cl)
         For i = 0 To UBound(Sp)
            If Sp(i) <> "NA" And Sp(i) <> "" Then
               .item(Sp(i)) = Empty
            End If
         Next i
      Next Cl
      knis80 = Join(.keys, Delim)
   End With
End Function

Used like
=knis80(F2,L2)

 

[knis80]

Thanks Fluff, it worked like magick! I really appreciate your will to help others.
Regards

 

[Fluff]

You're welcome & thanks for the feedback

 

[knis80]

Really! this saved my several hours of work, in past I'd spent uncountable hours do this task, you are really genius Fluff! Thanks!

To do that you would need

Function knis80(Rng1 As Range, Rng2 As Range, Optional Delim As String = " ") As String
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   With CreateObject("scripting.dictionary")
      For Each Cl In Union(Rng1, Rng2)
         Sp = Split(Cl)
         For i = 0 To UBound(Sp)
            If Sp(i) <> "NA" And Sp(i) <> "" Then
               .item(Sp(i)) = Empty
            End If
         Next i
      Next Cl
      knis80 = Join(.keys, Delim)
   End With
End Function

Used like
=knis80(F2,L2)