concatenating cell range data which meets criteria

[fredd2]

I have a spreadsheet with over 500 columns with individual headings, and cell data in those columns that can either be 1,2 or 3. I need to add a column that, for example, looks at each cell in row 2 and concatenates the column heading for each cell which has a value of 1. This would then give me a list in the first cell in row 2 of all the columns meeting that criteria. I can do this by using =concatenate(=if(b1=1,a1&2", ",null,etc and this does work until the limit on the number of characters in the formula is reached. Is there some way of achieving the same result by using a cell range instead, and if so, how?

 

[SkipVought]

Hi,

"I have a spreadsheet with over 500 columns with individual headings..."

WOW! This spreadsheet must be your full time job!

What is the business case for this requirement?



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredd2]

It is for work, each column being modules or courses. What I need to do is enter a ranking of 1, 2 or 3 against any module or course any staff member has done. Some cells will be filled in, others will be blank. What I then need is a list of the name of each module or course each member of staff has done, the ones ranked 1 in one column, the 2s in another, and the 3s in a third. I know it would be easier to do in Access, but the user who has asked for it can only use Excel, so I'm trying to find a way round the problem.

 

[SkipVought]

Then I have absolutely no idea why you are using COLUMNS???

That is not very good database design, where column heading names (field names) are DATA ELEMENTS.

You ought to SERIOUSLY reconsider your design. It seem that your table, from the scant information you have supplied, could be 3 columns...
[tt]
StaffID
ModuleID
Rating
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredd2]

The problem is my colleague doesn't want the table in a database, she wants it in excel, as she finds excel easier to use. So I'm having to try to make it work somehow.

What she wants is a spreadsheet with column 1 with names, columns showing total rankings and course names for those rankings, then columns for each individual module or course. The cells in these columns would have 1, 2 or 3 entered in them, but might be left blank. What she wants to be able to do is look across the row and see what course/module the person has been on and what they ranked it. That isn't a problem. What she also wants is to be able to see how many 1, 2 or 3 rankings any person has given, and a cell showing which courses have been given that rank. So if Mr Smith gave 4 courses a 1 ranking, and 3 courses a 2 ranking, then column 1 would have the name Mr Smith, column 2 would have 4 (being the total number of courses he ranked at 1, then column three would have the names of those 4 courses. What I am trying to do is find a way of concatinating the headings for the columns where Mr Smith has given the course a 1 ranking into a cell in column 3. I hope that makes more sense, as I can't explain it any better.

 

[SkipVought]

"I hope that makes more sense, as I can't explain it any better."

I said nothing about putting this in some other application. The idea of having over 500 COLUMNS of data and counting is absolutely absured. Excel is not designed to handle horizontal data very well. faq68-5184.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Given your current design, I cannot think of a formula that would do what you need. You will either nedd to use code or think about the way you store the data (as per Skip)

If you store data in excel but use general database best practice conventions, reporting generally becomes faaaaaar easier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GSCaupling]

Hi Fredd2,

Welcome to Tek-Tips.

Can you give us an idea of how many rows of data you have/anticipate? In your current design, the rows would be for staff members, correct?

I really recommend Skip's design above. All you need is Staff, Course and Rating for columns. PivotTables then can give you any analysis you need.

Good luck!
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[fredd2]

Sorry for the delay in replying to all your posts. It's been slightly manic at work.

I'm not sure that I understand the using of pivot tables, so I think it would probably be beyond my colleague as well. What I did discover on the internet was code which concatenates ranges. However, I can't find a way to select only certain values from that range, or to show the column headings instead of the value of the cells themselves.

As this has now become a coding question, I don't know whether it is the correct procedure to carry on with this thread, or to start a new one in the vba forum?

 

[SkipVought]

Please post a representative example that will help us envision the nature of your problem.

Post what IS and what you want it TO BE.

Copy 'n' paste from your sheet, please.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredd2]

Hi there,

The code I have found is:

Function concatenaterangemedium(Subject, Notes As Range) As String
Dim strTemp, sepTemp As String
Dim cel As Range
Dim toprow As Range
Dim cnt As Integer
strTemp = ""
For Each cel In Parts.Cells
If cel.Value = "" Or cel.Value = 0 Then
sepTemp = ""
Else
sepTemp = Separator
End If
strTemp = strTemp & sepTemp & cel.Value
Next cel
concatenaterangemedium = strTemp
End Function

What it does is concatenate a range of cell data in a row, and puts separators between each cell value.

What I would like it to do is only concatenate the cell values if they are a certain value. For example, only concatenate for cells with the value of 2. When I have tried amending the code I've ended up with all the values in, but the separator missed out after the value 2, which is totally wrong.

The other thing I would like to do, in the example of only the values of 2, is to return the column heading for that cell rather than the cell value. For example, modules xy123 and fg567 would each have a column with the module names as the headings. If those columns had been given a rating of 2 by a staff member, the concatenation would return xy123; fg567 rather than 2; 2.

Hope that's clear.

Thank you.

Fredd2

 

[SkipVought]

This is not the forum for VBA code. Forum707 instead.

But PLEASE post example data as I have previously requested.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredd2]

Sorry,

I pasted in the wrong code.

The correct one is:

Function concatenaterangemedium(Parts As Range, Separator As String)
Dim strTemp, sepTemp As String
Dim cel As Range
Dim toprow As Range
Dim cnt As Integer
strTemp = ""
For Each cel In Parts.Cells
If cel.Value = "" Or cel.Value = 0 Then
sepTemp = ""
Else
sepTemp = Separator
End If
strTemp = strTemp & sepTemp & cel.Value
Next cel
concatenaterangemedium = strTemp
End Function


Fredd2

 

[SkipVought]

Hellloooooooooooo!!!

Are you reading the posts in this thread???

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The other thing I would like to do, in the example of only the values of 2, is to return the column heading for that cell rather than the cell value. For example, modules xy123 and fg567 would each have a column with the module names as the headings. If those columns had been given a rating of 2 by a staff member, the concatenation would return xy123; fg567 rather than 2; 2.

Please post an example of data that you want transformed and how you want it to look after you're done.

NOT WORDS!

An example is...
[tt]
Heading1 Heading2 Heading3
dataa1 datab1 datac1
dataa2 datab2 datac2
[/tt]
result...
[tt]
Heading1 dataa1 dataa2
Heading2 datab1 datab2
Heading3 datac1 datac2
[/tt]
Now that's an example that means omething!



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredd2]

I have been reading the posts in the thread, but my second posting today with corrected code crossed your very swift response to my first posting. However, I have now decided to use the forum707: VBA Visual Basic for Applications (Microsoft) instead, as per your reply to my orginal query tonight as to whether to carry on with this thread or switch to a different forum.

Thank you for you help.

 

[GSCaupling]

Hi fredd2,

If you can write/understand code, you can do PivotTables. They are "wizard" driven, user friendly and very powerful.

I realize you many may not want to use them this time, but when you get a chance I would recommend getting familiar with them. Like I say, if you can code, PT's will be like going to kindergarten.

I won't be following over to Forum 707. They talk funny over there, always "dim" this and "dim" that. I get paranoid and assume they're talking about me...

Best of luck!
GS


[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]