Help with If or Concentrate forumla

[JasonNeedsHelp]

Hello All,

I'm trying to create a worksheet where if I find an error i just put "X" into a square.

In a comments Column i would like a summary of all the "X"s that I marked for a particular row.

See attached. Worksheet.xlsx

In my example in Row 3 i would have placed X in column B, G and J. In column A i want the text to read:

"Logs Missing
Vehicle Number Missing
Name Not Printed"

but each spaced one down so that they don't colide and become

"Logs MissingVehicle Number MissingName Not Printed"

Not sure if this is an IF formula or a Concentrate formula or combination of.

Any help is appreciated.

Thanks!

[Glenn Kennedy]

To do that you need a bit of VBA (see module 1 in view code) "borrowed" from Chip Pearson, and a UDF called String Concat - which needs to be set as an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

=StringConcat(","&CHAR(10),IF(B3:AH3="X",$B$1:$AH$1,""),"")

Remember to enable macros when opening....

[AlKey]

Enter formula in B3, copy across and down

Please Login or Register  to view this content.

[Glenn Kennedy]

Hi... We read this one completely dfifferently!! I assumed that the OP wanted us to assemble the summary, not populate the sheet with x's....

[JasonNeedsHelp]

Hi All,

Alkey - Sorry if my direction lead to some confusion. Glenn's inturpreted my intentions was correct as I will be populating X's as I review logbooks but would want a summary for quick reference back to the particular Row.

Glenn - Sorry but i thought this would have been a formula that I could copy into my cells and I do not know how to replicate the VBA code you gave me. I posted a simplified sheet to avoid adding too much information. Attached is the actual worksheet i would like to use.... could you insert your macro into here. Also, i'll be duplicating the worksheet many times over... will this only work on all sheets?

2016 LGE HOS Worksheet.xlsm

Thanks again!

[Glenn Kennedy]

If you want all the results in the same cell - you really don't have a choice but to use something like this... Once the code is in place on this workbook, it will work for all sheets in the workbook.

Have a go. I have added it to this.

to view the code, right click on a Tab name and navigate to module 1.

[JasonNeedsHelp]

If you want all the results in the same cell - you really don't have a choice but to use something like this... Once the code is in place on this workbook, it will work for all sheets in the workbook.

Have a go. I have added it to this.

to view the code, right click on a Tab name and navigate to module 1.

Hi Glenn,

Mind moving the VBA into this sheet?
2016 LGE HOS Worksheet.xlsm

Sorry, i don't know much about VBA's

Thanks,

[Glenn Kennedy]

It's done...

[Glenn Kennedy]

Oh Cr@p. I attached the wrong sheet!!!!

[Jacc]

Glenn, that's a lot of code! I wrote something similar a long time ago before I realized that it's better to do most of the work with formulas and as little as possible with code. My opinion anyway. All you need VBA to do is concatenate which is what this code snippet does. Maybe I should have included some error handler of some sort but I can't think of what that would be right now.

So this array formula:

Please Login or Register  to view this content.

where ConcatY is a UDF looking like this:

Please Login or Register  to view this content.

The array formula has to be entered with Ctrl + Shift + Enter of course.

Edit: Darn! It should be Function ConcatY(FinalRng As Variant) As String. Not that anyone will notice any difference but it's better to make it right.

[Glenn Kennedy]

Jacc, I make no bones about it. I'm no good at all with VBA. I can copy others, but that's all!! But yours is much neater, by far.

[Jacc]

Thanks! The IFERROR was only necessary for the first sample workbook as there were errors, so the formula is in fact even neater.

[JasonNeedsHelp]

Glenn, that's a lot of code! I wrote something similar a long time ago before I realized that it's better to do most of the work with formulas and as little as possible with code. My opinion anyway. All you need VBA to do is concatenate which is what this code snippet does. Maybe I should have included some error handler of some sort but I can't think of what that would be right now.

So this array formula:

Please Login or Register  to view this content.

where ConcatY is a UDF looking like this:

Please Login or Register  to view this content.

The array formula has to be entered with Ctrl + Shift + Enter of course.

Edit: Darn! It should be Function ConcatY(FinalRng As Variant) As String. Not that anyone will notice any difference but it's better to make it right.

Perfect,

Thanks everyone!

[Jacc]

''''''''''