[SOLVED] Conditionally collect data in one cell?

**GenieNitro** · 11-13-2008, 01:34 PM

Hello there,

I'm trying to make a spreadsheet that looks up values for a number of individuals and if they meet certain criteria they are collected in a box.

It is for a class of children's test results. I would like to be able to write the results each child got across the page (names at side, headings across top). If a child gets, say, level 2a in his test, his name appears in a cell elsewhere. I can do this with an 'IF' statement... Thing is I want to collect all the children who got that level in the other cell. The cell might end up with 10 or 12 names in it as a result of their scores being looked up. I would have cells for all of the levels, so the children can be automatically grouped together by attainment.

This is rather beyond my capabilities! Is it doable - and if so, can anyone help?

Thanks very much in advance!

Steve

**GenieNitro** · 11-13-2008, 05:18 PM

Hi, I've figured a way of nearly getting what I want... I think its a bit long winded though...

this is what I've done:
=IF(B11="2b",A11&" ","")&IF(B12="2b",A12&" ","")&IF(B13="2b",A13&" ","")&IF(B14="2b",A14&" ","")&IF(B15="2b",A15&" ","")&IF(B16="2b",A16&" ","")&IF(B17="2b",A17&" ","")&IF(B18="2b",A18&" ","")&IF(B19="2b",A19&" ","")

Seems to work!

Now - is it possible to add up the number of names that appears in this cell and send the answer to another cell?

cheers

Steve

**JBeaucaire** · 11-13-2008, 05:31 PM

Please Login or Register  to view this content.

Change the cell reference to where your names are collecting.

**JBeaucaire** · 11-13-2008, 05:36 PM

And a slight aesthetic adjustment to your code, puts in a comma and space and adds it ahead of the name when it's used instead of after. For your consideration:

Please Login or Register  to view this content.

**GenieNitro** · 11-13-2008, 06:55 PM

Originally Posted by JBeaucaire

And a slight aesthetic adjustment to your code, puts in a comma and space and adds it ahead of the name when it's used instead of after. For your consideration:

Please Login or Register  to view this content.

Thanks, will try that...

Actually, my code has come up with a bit of a problem.
The data this code is reading is actually on a different worksheet (at the beginning of the workbook). The code has come out so long that I can only get it to address 28 students before the 'formula is too long'. Unfortunately, our classes sometimes go above 30 (maybe to 32/33). Any way of trimming the length a bit? (A have already changed the name of the data worksheet to simply '1' but it's still too long!

cheers

Steve

**JBeaucaire** · 11-13-2008, 07:04 PM

I was wondering about that as I read it. You need a souped up version of the Concatenation function. You'll have to create it. The benefit of creating your own UserDefinedFunction is you get some new powerful and short tools for concatenating a large string of data.

CPearson has written a UDF called StringConcat that you can access the code for here:
http://www.cpearson.com/excel/StringConcatenation.aspx

Follow the instructions CAREFULLY for getting that UDF into your sheet.

Once it's in, your new formula would be much simpler. Let's say your 30 kids names are all in A2:A31 and the "2d" flags are in C2:C31, your new code would simply be:

Please Login or Register  to view this content.

This is an array formula, so you have to press CTRL-SHIFT-ENTER instead of just ENTER. Is that short enough for you? Hehe.

Good luck!

**GenieNitro** · 11-13-2008, 07:16 PM

Thanks for that!

HAve to say - ooerr! Never done anything with visual basic before!

I downloaded the bas module file but I can't find the instructions on how to include it in my spreadsheet... Probably being a bit thick!

Steve

**JBeaucaire** · 11-13-2008, 07:26 PM

No, follow these instructions on the page for manually inserting it. The code is there, just cut and paste. Everything between Function StrongConcat and all the way down to End Function. Here you go:

How to Create Excel User Defined Functions

1. Open up your workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the your code
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.
7. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...).

Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet. Best to store those UDFs in the sheets themselves.

(Source)