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

knis80

New Member
Joined
Aug 11, 2019
Messages
9
Hi,
I am creating a spreadsheet with several values (one Text value in each cell) i.e.

Row1Col. ACol. BCol. CCol. DCol. E (The Result)
Row2AppleBananaNAPumpkinNA
Row3CherryAppleMangoPineappleNA
Row4AppleBananaNAPumpkinApple

<tbody>
</tbody>










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

Row1Column F
Row2Apple Banana Pumpkin(Without the text “NA”)
Row3Cherry Apple Mango Pineapple(Without the text “NA”)
Row4Apple Banana Pumpkin(Without the text “NA” and Ignoring the Double Value “Apple”)

<tbody>
</tbody>















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!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
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
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Col. A</td><td style=";">Col. B</td><td style=";">Col. C</td><td style=";">Col. D</td><td style=";">Col. E (The Result)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Apple</td><td style=";">Banana</td><td style=";">NA</td><td style=";">Pumpkin</td><td style=";">NA</td><td style=";">Apple
Banana
Pumpkin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cherry</td><td style=";">Apple</td><td style=";">Mango</td><td style=";">NA</td><td style=";">Banana</td><td style=";">Cherry
Apple
Mango
Banana</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Apple</td><td style=";">Banana</td><td style=";">NA</td><td style=";">Pumpkin</td><td style=";">Apple</td><td style=";">Apple
Banana
Pumpkin</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Delete</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=knis80(<font color="Blue">A2:E2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

knis80

New Member
Joined
Aug 11, 2019
Messages
9
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
If you format the cells for Wrap text, then you will see each word on a new line.
Alternatively you can use
Code:
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
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Col. A</td><td style=";">Col. B</td><td style=";">Col. C</td><td style=";">Col. D</td><td style=";">Col. E (The Result)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Apple</td><td style=";">Banana</td><td style=";">NA</td><td style=";">Pumpkin</td><td style=";">NA</td><td style=";">Apple, Banana, Pumpkin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cherry</td><td style=";">Apple</td><td style=";">Mango</td><td style=";">NA</td><td style=";">Banana</td><td style=";">Cherry - Apple - Mango - Banana</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Apple</td><td style=";">Mango</td><td style=";">NA</td><td style=";">Pumpkin</td><td style=";">Apple</td><td style=";">Apple
Mango
Pumpkin</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Delete</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=knis80(<font color="Blue">A2:E2,", "</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=knis80(<font color="Blue">A3:E3," - "</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">=knis80(<font color="Blue">A4:E4,CHAR(<font color="Red">10</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

knis80

New Member
Joined
Aug 11, 2019
Messages
9

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
To do that you would need
Code:
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

New Member
Joined
Aug 11, 2019
Messages
9

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

knis80

New Member
Joined
Aug 11, 2019
Messages
9
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
Code:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,628
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top