Reputation:
Auto-concatenate in Excel
I have a huge (~950 variables) survey response spreadsheet, made 2-4x larger than it needs to be because each item's individual response options are reported in separate columns. E.g., If Question 2 is in cell A1 and has 3 response options, these are listed below the question in cells A2-C2, A3-C3, etc. Note: Only one of A2-C2, etc. is filled w/ data for each observation.
It seems clear that I could go through the spreadsheet manually and concatenate A2-C2 using code similar to this:
=CONCATENATE(IF(ISBLANK(A4),"",A4),IF(ISBLANK(B4),"",B4),IF(ISBLANK(C4),"",C4))
But, the above requires manually altering the code (questions have anywhere from 2-6 response options) and copying/pasting it through all observations for each of the 960 variables individually. Because I'd like to finish this before dying of old age, I'd love to have some way of automating the above process.
It would be easy enough to go through and insert an identifier column (w/ no data but w/ some obvious 1st row name) after each variable's last response option so the code knows where to begin looking back for data to concatenate. The code would have to know to stop whenever it hits the previously concatenated result, and begin the current concatenation w/ the subsequent column. Once it hits a certain line (say, 60? I only have ~55 observations), it could just look for the next identifier column and repeat the process.
Any ideas would be much appreciated.
I need to check for blanks so as to not have extra spaces in the data (to aid future analysis).
Hopefully the below will clarify the situation further. You're correct in your earlier comment; each question is followed by 55 rows of observations. For example:
| | Q1 | | || Q2 | ||
|-|--------|---------|--------||---------|--------||
|1| 1 | | || | 2 ||
|2| | 2 | || | 2 ||
|3| | | 3 || | 2 ||
|4| | 2 | || 1 | ||
|5| | | 3 || | ||
|6| 1 | | || | 2 ||
|7| 1 | | || 1 | ||
|8| | | 3 || 1 | ||
|9| | 2 | || | 2 ||
There isn't currently a blank column after the last response option for each question, but (as mentioned in the initial post) I can easily throw one in.
Upvotes: 1
Views: 11939
Answers (6)
Reputation: 1
Or if you want just a simple way to select a horizontal range and have it concatenate the non-blank cells:
Function CONCIF(rng As Range)
Dim rCell As Range
Dim concat As String
Dim dist As Integer
dist = 0
concat = ""
For Each rCell In rng
If rCell.Value <> "" Then
If concat = "" Then
concat = rCell.Value
Else
concat = concat & ", " & rCell.Value
End If
End If
Next rCell
CONCIF = concat
End Function
Upvotes: 0
Reputation: 3486
Old post but I made this function to concatenate cells. Works similar to SUMIF.
Function CONCIF(rng As Range, criteria As Range, sums As Range)
'Function to concatenate a range of cells if the chosen adjacent cells matches
'the criteria.
'To use:
' Copy and Paste this into a module in VB Editor
' In a cell type =CONCIF(rng, criteria, sums) where:
' rng is the range of cells to match the criteria
' criteria is the value you would to match
' sums is the range of cells to concatenate if criteria matches
' To change what is put between the concatenations, edit the " / " below and put
' whatever you would like in between the quotes.
' Enjoy! -RP
Dim rCell As Range
Dim concat As String
Dim dist As Integer
dist = sums.Column - rng.Column
concat = ""
For Each rCell In rng
If rCell = criteria Then
If concat = "" Then
concat = rCell.Offset(0, dist).Value
Else
concat = concat & " / " & rCell.Offset(0, dist).Value
End If
End If
Next rCell
CONCIF = concat
End Function
Upvotes: 0
Reputation:
Thanks for the piece of information. Apart from your way one can also use the & character for concatenation, so instead of: =concatenate(B1,” “,C1,” “,D1) you could use: =B1 & ” ” & C1 & ” ” & D1
Upvotes: 0
Reputation: 119106
I'm not 100% sure I understand your layout, but you can greatly simplify the concatenation by using & as follows:
=A4&B4&C4
this would have the same effect as your code, and does not require the checks for blank cells.
I'll tackle the rest of the question as information becomes available.
edit: Here's my solution. I'm assuming that the questions are in row 1, and the first set of observations is in row 2, so the last set of observations falls in row 56.
- type the number 1 into the cell A58
- place the formula: =IF(ISBLANK(B1),A58,A58+1) into cell B58
- fill this formula right all the way across all of your questions
- in cell A59, enter the formula =MATCH(COLUMN(),58:58,0)
- in cell A60, enter the formula =MATCH(COLUMN(),58:58,1)
- in cell A61, enter the formula =INDEX(1:1,,A59)
- fill all three of these formulas right for 17 cells (assuming you have 17 questions)
- in cell A62, enter the formula =SUM(INDIRECT(ADDRESS(ROW(A2),A$59)&":"&ADDRESS(ROW(A2),A$60)))
- fill this formula across 17 cells, and down 55 cells.
Here's how it would look for three questions:
| A | B | C | D | E | F | G |
|----|------------------------------------------
| 1 | Qn1 Qn2 Qn3
| 2 | 1 2 1
| 3 | 2 2 2
| .. |
| 58 | 1 1 1 2 2 3 3
| 59 | 1 4 6
| 60 | 3 5 8
| 61 | Qn1 Qn2 Qn3
| 62 | 1 2 1
| 63 | 2 2 2
Upvotes: 1
Reputation: 1640
The fastest way:
- Add an new column after column D
- Put 'Q1' in cell B1
- Put the following formula in: =SUM(B2:D2)
- Copy the formula down to the last row
- Repeat the steps above for all questions
- Select all data (ctrl-*)
- Copy the selection
- Choose 'Paste Special' from the context menu and choose 'values'
- Delete the original columns
If you want you can create a macro that does this automatically.
Here is the macro. It is my no means my best piece of coding ever. What would you expect in 15 minutes. It does the job, although it crashes when finished. ;o)
- Open your excel sheet
- Make a back up copy
- Hit Alt-F11
- Insert a new module
- Paste the code below
- Put the cursor inside the macro
- Hit F8 to step through the code
Since you are in Stack Overflow I assume you will be able to adjust the macro to further tailor your needs.
Sub Main()
Dim ColumnsCount As Integer
ColumnsCount = Range("A1").CurrentRegion.Columns.Count
For i = 2 To 20000
Dim CurrentCell As Range
Set CurrentCell = Range("A1").Offset(0, i - 1)
If CurrentCell.Value <> "" Then
CurrentCell.Select
Selection.End(xlToRight).Select
Dim AnswersCount As Integer
AnswersCount = Selection.Column - CurrentCell.Column
CurrentCell.Offset(0, AnswersCount).Select
Selection.EntireColumn.Insert
Selection.Value = CurrentCell.Value
i = i + AnswersCount
Selection.Offset(1, 0).Select
Selection.FormulaR1C1 = "=SUM(RC[" + CStr(AnswersCount * -1) + "]:RC[-1])"
Selection.Copy
Range(Selection, Selection.Offset(100, 0)).Select
ActiveSheet.Paste
Selection.EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next i
End Sub
Upvotes: 0
Reputation:
@EJames: Thanks for the suggestion & pointer to lay out the example graphically. I need to check for blanks so as to not have extra spaces in the data (to aid future analysis).
Hopefully the below will clarify the situation further. You're correct in your earlier comment; each question is followed by 55 rows of observations. For example:
| | Q1 | | || Q2 | ||
|-|--------|---------|--------||---------|--------||
|1| 1 | | || | 2 ||
|2| | 2 | || | 2 ||
|3| | | 3 || | 2 ||
|4| | 2 | || 1 | ||
|5| | | 3 || | ||
|6| 1 | | || | 2 ||
|7| 1 | | || 1 | ||
|8| | | 3 || 1 | ||
|9| | 2 | || | 2 ||
There isn't currently a blank column after the last response option for each question, but (as mentioned in the initial post) I can easily throw one in.
Much obliged.
Upvotes: 0