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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Nr[/TD]
[TD]Inprint[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]66[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]23[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]50[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Customer D[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Customer E[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Customer F[/TD]
[TD]27[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Customer G[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]














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