Concatenate question clarification...

joebock

New Member
Joined
Nov 2, 2002
Messages
5
I was advised to revise my question because maybe I wasn't clear enough...Here goes:

I have 4 separate Excel Workbook Files. Each file contains between 3 and 8 worksheets. The worksheets contain a yearly calendar showing weekends and holidays. The secretary or supervisor brings up an individual employees sheet, and they use a validation list dropdown box to select vacation days, personal day, etc. The calendar then can, at a glance, show the employees scheduled attendance for the entire year. Now, my boss has the secretary take the data off of these vacation record/attendance sheets and place them on a monthly calendar. This way, he can see the entire month and work coverage at one glance. This activity takes the secretary an inordinate amount of time, because of the constant changes being made with the individual employees vacations. So, what I am trying to do is have a sheet collect the names of all employees scheduled to be off any day of the year. I have it so that I put the dates of the month in separate columns. Under the columns, I have a formula in the cells underneath which goes out to the employee file and checks whether they have any 'V' (vacation) scheduled in the corresponding cell. If a 'V' does exist, then it puts the person name underneath the date column. I have approximately 30 cells underneath each date corresponding to each employee. For example:

November
1 2 3 4 5 6
Smith
Brown
Jones

Fred

Bob

The blanks represent any time the employee sheet shows that the employee referenced in that cell is scheduled to be in that day. Anyway, collecting this data is not my problem.

My problem is that I am using concatenate to take all of the values in each column and am trying to put them into a calendar template so that for any particular date, it will show a word wrapped list of anyone scheduled for vacation that day. Now, right now when I concatenate, I have to select the range and put dividing characters (", ") between each value. The problem is that I cannot get Excel to avoid the null values and just add the names to the concatenated list. What I wind up with is something like the following:

Smith, Brown, Jones, , Fred, , Bob

I would like to get it so that it reads:

Smith, Brown, Jones, Fred, Bob

Now, it's scary, but I am the most literate Excel person in my section and I need to make this as foolproof/hands-off as possible. I know I could have the secretary sort the list for each day and then modify the formula in the calendar to select the range corresponding to the text, but this would confuse her completely.

Anyway, that is my problem, I have tried to clarify it. Please let me know if I have just confused the issue.

Thanks for any help you can give me...

Joe
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
Seems that an additional IF statement might solve the problem. The IF statement would determine whether there is anything in that cell, if not, then the formula would not apply, otherwise the "Name, "

Something to that effect.
 

joebock

New Member
Joined
Nov 2, 2002
Messages
5
Ok, shades, I thought of that, but I couldn't imagine how to make it work...I assume I would try:

IF(A1<>" ", concatenate A1,...

I can't see how to make it loop back and add values to the concatenate statement...If I could do that, then my problems would be solved...

Thanks,

Joe
 

joebock

New Member
Joined
Nov 2, 2002
Messages
5
Alright, after alot of pain, I think I figured it out. I went to a separate sheet and embedded IF statements into the CONCATENATE function. I can only do 15 or so statements at a time, so I have three cells which collect all the possible data. I then goto a fourth cell and do a TRIM on the results. Basically, my formulas look like this:

In Cell A1
=CONCATENATE((IF(SUMMARY!A3<>EXACT(SUMMARY!A3," "),SUMMARY!A3&" ",)),(IF(SUMMARY!A4<>EXACT(SUMMARY!A4," "),SUMMARY!A4&" ",)),(IF(SUMMARY!A5<>EXACT(SUMMARY!A5," "),SUMMARY!A5&" ",)),(IF(SUMMARY!A6<>EXACT(SUMMARY!A6," "),SUMMARY!A6&" ",)),(IF(SUMMARY!A7<>EXACT(SUMMARY!A7," "),SUMMARY!A7&" ",)),(IF(SUMMARY!A8<>EXACT(SUMMARY!A8," "),SUMMARY!A8&" ",)),(IF(SUMMARY!A9<>EXACT(SUMMARY!A9," "),SUMMARY!A9&" ",)),(IF(SUMMARY!A10<>EXACT(SUMMARY!A10," "),SUMMARY!A10&" ",)),(IF(SUMMARY!A11<>EXACT(SUMMARY!A11," "),SUMMARY!A11&" ",)),(IF(SUMMARY!A12<>EXACT(SUMMARY!A12," "),SUMMARY!A12&" ",)),(IF(SUMMARY!A12<>EXACT(SUMMARY!A12," "),SUMMARY!A12&" ",)),(IF(SUMMARY!A13<>EXACT(SUMMARY!A13," "),SUMMARY!A13&" ",)),(IF(SUMMARY!A14<>EXACT(SUMMARY!A14," "),SUMMARY!A14&" ",)),(IF(SUMMARY!A15<>EXACT(SUMMARY!A15," "),SUMMARY!A15&" ",)),(IF(SUMMARY!A16<>EXACT(SUMMARY!A16," "),SUMMARY!A16&" ","")))

In Cell A2
=CONCATENATE((IF(SUMMARY!A17<>EXACT(SUMMARY!A17," "),SUMMARY!A17&" ",)),(IF(SUMMARY!A18<>EXACT(SUMMARY!A18," "),SUMMARY!A18&" ",)),(IF(SUMMARY!A19<>EXACT(SUMMARY!A19," "),SUMMARY!A19&" ",)),(IF(SUMMARY!A20<>EXACT(SUMMARY!A20," "),SUMMARY!A20&" ",)),(IF(SUMMARY!A21<>EXACT(SUMMARY!A21," "),SUMMARY!A21&" ",)),(IF(SUMMARY!A22<>EXACT(SUMMARY!A22," "),SUMMARY!A22&" ",)),(IF(SUMMARY!A23<>EXACT(SUMMARY!A23," "),SUMMARY!A23&" ",)),(IF(SUMMARY!A24<>EXACT(SUMMARY!A24," "),SUMMARY!A24&" ",)),(IF(SUMMARY!A25<>EXACT(SUMMARY!A25," "),SUMMARY!A25&" ",)),(IF(SUMMARY!A26<>EXACT(SUMMARY!A26," "),SUMMARY!A26&" ",)),(IF(SUMMARY!A27<>EXACT(SUMMARY!A27," "),SUMMARY!A27&" ",)),(IF(SUMMARY!A28<>EXACT(SUMMARY!A28," "),SUMMARY!A28&" ",)),(IF(SUMMARY!A29<>EXACT(SUMMARY!A29," "),SUMMARY!A29&" ",)),(IF(SUMMARY!A30<>EXACT(SUMMARY!A30," "),SUMMARY!A30&" ",)),(IF(SUMMARY!A31<>EXACT(SUMMARY!A31," "),SUMMARY!A31&" ","")))

In Cell A3
=CONCATENATE((IF(SUMMARY!A32<>EXACT(SUMMARY!A32," "),SUMMARY!A32&" ",)),(IF(SUMMARY!A33<>EXACT(SUMMARY!A33," "),SUMMARY!A33&" ",)),(IF(SUMMARY!A34<>EXACT(SUMMARY!A34," "),SUMMARY!A34&" ",)))

In Cell A4
=TRIM(A1&A2&A3)

This gives me exactly what I was looking for! Kind of a brute force method, but without doing any VBA stuff, it is the only way I can figure to do it...

I appreciate the effort of the people that tried to help. Maybe my post can help another VBA-difficient individual...

Joe
 

Forum statistics

Threads
1,143,921
Messages
5,721,549
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top