Total (or summing) only 'select' row(s) into a list ???

[BaLLZaCH]

Thank you for wanting to help,
I am trying to ''total'' a set of rows like this: (Not adding +)

I want to take the 'numbers' or 'codes' entered in columns to 'CONSOLIDATE' into a cell with commas (OR SPACES) such as "1, 3, 4, 7" but at the same time ONLY use the codes that are actually entered (Meaning, if a cell is blank or 0, it will not include it in the consolidation.

Example:
[Before]
column1 column2, 3, 4, 5, ....
("Displays Codes Selected")---(code1)(Code2)(code3)(etc...)

[After]
column1
(100, 300, 400, 700)





Am I being too confusing ?
What I want to do is a simple idea, i just dont know to explain it better right now ....

Thanks !

[NBVC]

If you download and install the free Morefunc addin from the following site, you can apply the formula shown below: http://xcell05.free.fr/english/

=SUBSTITUTE(MCONCAT(IF(A4:G4>0,A4:G4," "),","),", ","")

Where A4:G4 is the range of codes to concatenate.

The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER for it to work correctly.

[BaLLZaCH]

Cool, I appriciate that.
Now I have a more complicated question:


The codes that I use look like this:

257
258
259
260


But the total exists like this: 1, 2, 3, or 4.

257 means 1, 258 means 2, 259 means 3, so on up to 6.


So, if the total is 4, is there a way for that consolidation to enter "260, " in the consolidation cell ?


Thanks again!

[olasa]

This might be what you want. I couldn't quite understand what it was you wanted (see encl.).

The basic structure:
=start segment & 2nd segment & 3rd segment & ....
=IF(A1>0,VLOOKUP(A1,$A$5:$B$11,2,0),"")& IF(B1>0,IF(COUNTA($A$1:A1)>0," ,","")&VLOOKUP(B1,$A$5:$B$11,2,0),"")&....

//Ola

[BaLLZaCH]

Thank you Ola, that was exactly what I was asking. Works great on your worksheet.



However, I think I just realized this is going to be rediculous'ly complicated to do what it needs to do. Unless there is an easier way, I think I should give up.

For the example I gave, and you showed me, this is perfect. BUT, I have to do this not just for 1 set of columns, but for all 20. All 20 columns have different sets of rules (Such as the 257, 258 example )

The book3 example you gave me works perfect for the first column, the '257-262' rules, but now on the 2nd column is the same concept, but the differnce is the codes are 250 to 256, and they must also consolidate into the consolidation cell.


The end result would look like:

259, 251, etc... etc...


I attached the beta version of my worksheet tab to give a visual reference at what I wanted to acomplish (But I think it is a little out of my reach, crap...)


Here is a quick reference of how stupid these codes are,
'CS005' is the code for range 257-262. If a 259 is billed in the 'resolution code' section, this would translate to 3 cs005's.

The same goes for CS004. This is for range 250-256. "4" cs004's would be a '253' code.


So if I entered 3 cs005's, and 4 cs004's, the consolidation would look like:
"259, 253"




I should give up, huh ?
Thanks !!!!

[NBVC]

If your not adverse to using VBA code (perhaps attached to a button) then you can try this code (which may not be the most efficient, but works) attached to a button for refreshing.

Please Login or Register  to view this content.

You would have to create a table on the side which lists all your codes and corresponding min values....

Each time changes are made, you would have to click the Refresh button for updates....