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.

[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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

Book1
ABCDEG
1Col. ACol. BCol. CCol. DCol. E (The Result)
2AppleBananaNAPumpkinNAApple Banana Pumpkin
3CherryAppleMangoNABananaCherry Apple Mango Banana
4AppleBananaNAPumpkinAppleApple Banana Pumpkin
Delete
Cell Formulas
RangeFormula
G2=knis80(A2:E2)
 
Last edited:
Upvote 0
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
 
Upvote 0
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

Book1
ABCDEF
1Col. ACol. BCol. CCol. DCol. E (The Result)
2AppleBananaNAPumpkinNAApple, Banana, Pumpkin
3CherryAppleMangoNABananaCherry - Apple - Mango - Banana
4AppleMangoNAPumpkinAppleApple Mango Pumpkin
Delete
Cell Formulas
RangeFormula
F2=knis80(A2:E2,", ")
F3=knis80(A3:E3," - ")
F4=knis80(A4:E4,CHAR(10))
 
Last edited:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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