IF(Column X == value or value or value or value) write Column Y to F2 seperated by comma

[MrPowerQuery]

Hi there,

i looking for a formula or Language M code snippet or macro that does the following thing.

If in column "Sort" the number is either 66 or 46 or 23 or 19 or 18 or 6 write the value of column "Count" to field "F2" but seperated by a comma.

I tried some formula but didnt get the results i need.

Does someone have a idea?

greetz

 

[MrPowerQuery]

This is an example of the table. The real list has about 7.000 Rows

Customer	Nr	Inprint
Customer A	66	1
Customer B	23	2
Customer C	50	3
Customer D	6	4
Customer E	6	5
Customer F	27	6
Customer G	9	7

<tbody>
</tbody>

Result should be like: "1, 4, 5"

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG16Aug05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .Add 66, ""
    .Add 46, ""
    .Add 23, ""
    .Add 19, ""
    .Add 18, ""
    .Add 6, ""
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        nstr = nstr & IIf(nstr = "", Dn.Offset(, 1).Value, ", " & Dn.Offset(, 1).Value)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("F2").Value = nstr
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MrPowerQuery]

Hi MickG,

this worked perfect.
Thanks a lot!!!!

Try this:-

[COLOR=Navy]Sub[/COLOR] MG16Aug05
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, nstr [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
    .Add 66, ""
    .Add 46, ""
    .Add 23, ""
    .Add 19, ""
    .Add 18, ""
    .Add 6, ""
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        nstr = nstr & IIf(nstr = "", Dn.Offset(, 1).Value, ", " & Dn.Offset(, 1).Value)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
Range("F2").Value = nstr
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

You're welcome