combine multiple columns keeping some columns static

[lramesh]

This is a sample of the data I have


I would like to have my data like this - Image titled target file


Please let me know how this can be done with or without macros.

Source data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Staffname[/TD]
[TD]Empname[/TD]
[TD]EmpID[/TD]
[TD]Project1[/TD]
[TD]Domain[/TD]
[TD]Subdomain[/TD]
[TD]Project2[/TD]
[TD]Domain[/TD]
[TD]Subdomain[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AX[/TD]
[TD]1[/TD]
[TD]WER[/TD]
[TD]WERD[/TD]
[TD]WERS[/TD]
[TD]PQR[/TD]
[TD]PQRS[/TD]
[TD]PQRA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BA[/TD]
[TD]2[/TD]
[TD]VAR[/TD]
[TD]VARD[/TD]
[TD]VARS[/TD]
[TD]EWR[/TD]
[TD]EWRS[/TD]
[TD]EWRA[/TD]
[/TR]
</tbody>[/TABLE]

Target data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Staffname[/TD]
[TD]Empname[/TD]
[TD]EmpID[/TD]
[TD]Projects[/TD]
[TD]Domain[/TD]
[TD]Subdomain[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AX[/TD]
[TD]1[/TD]
[TD]WER[/TD]
[TD]WERD[/TD]
[TD]WERS[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AX[/TD]
[TD]1[/TD]
[TD]PQR[/TD]
[TD]PQRS[/TD]
[TD]PQRA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BA[/TD]
[TD]2[/TD]
[TD]VAR[/TD]
[TD]VARD[/TD]
[TD]VARS[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BA[/TD]
[TD]2[/TD]
[TD]EWR[/TD]
[TD]EWRS[/TD]
[TD]EWRA[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

lramesh,

Welcome to the MrExcel Forum.

What version of Excel are you using?

I assume that worksheets Source and Target already exist.

The macro will adjust for a varying number of rows and columns in worksheet Source.

Sample worksheets:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Staffname	Empname	EmpID	Project1	Domain	Subdomain	Project2	Domain	Subdomain
2	A	AX	1	WER	WERD	WERS	PQR	PQRS	PQRA
3	B	BA	2	VAR	VARD	VARS	EWR	EWRS	EWRA
4
Source

Excel 2007
	A	B	C	D	E	F
1	Staffname	Empname	EmpID	Projects	Domain	Subdomain
2
3
4
5
6
Target

After the macro using two arrays in memory in worksheet Target:

Excel 2007
	A	B	C	D	E	F
1	Staffname	Empname	EmpID	Projects	Domain	Subdomain
2	A	AX	1	WER	WERD	WERS
3	A	AX	1	PQR	PQRS	PQRA
4	B	BA	2	VAR	VARD	VARS
5	B	BA	2	EWR	EWRS	EWRA
6
Target

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 10/15/2013
' http://www.mrexcel.com/forum/excel-questions/733007-combine-multiple-columns-keeping-some-columns-static.html
Dim s As Variant, t As Variant
Dim i As Long, ii As Long, c As Long, n As Long
Dim lr As Long, lc As Long
With Sheets("Source")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  s = .Range(.Cells(2, 1), .Cells(lr, lc))
  n = (((lc - 3) / 3) * UBound(s, 1))
  ReDim t(1 To n, 1 To 6)
End With
For i = 1 To UBound(s, 1)
  For c = 4 To UBound(s, 2) Step 3
    ii = ii + 1
    t(ii, 1) = s(i, 1): t(ii, 2) = s(i, 2): t(ii, 3) = s(i, 3)
    t(ii, 4) = s(i, c): t(ii, 5) = s(i, c + 1): t(ii, 6) = s(i, c + 2)
  Next c
Next i
With Sheets("Target")
  .UsedRange.ClearContents
  .Range("A1").Resize(, 6).Value = [{"Staffname","Empname","EmpID","Projects","Domain","Subdomain"}]
  .Range("A2").Resize(UBound(t, 1), UBound(t, 2)) = t
  .Columns.AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[lramesh]

Hi,

Thanks for the macro. I changed the name of source and target sheets from my file and ran the macro.

I got a run time error - Subscript out of range

I had to include a few other column names that were static.

Please let me know what can be done.

thanks

 

[lramesh]

I tried with the same sample data I had provided. It works. When I change the number of columns (I changed it to 8 static columns that will remain constant and 3 columns that will contain the combined data), it does not work. Though the macro executes, the data is not aligned with the corresponding columns.

Hi,

Thanks for the macro. I changed the name of source and target sheets from my file and ran the macro.

I got a run time error - Subscript out of range

I had to include a few other column names that were static.

Please let me know what can be done.

thanks

 

[hiker95]

lramesh,

In order to continue I will need to see your workbook.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

lramesh,

I will not be available to assist you until this coming Sunday/Monday.

If you can not wait until then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[lramesh]

Here is the link I have updated to Box. I have 2 files -
Source_main - contains the source data
target_main - target data

Let me know if you can view it.

https://app.box.com/s/ns2kci989swgpu8un0q6

 

[lramesh]

Re: combine multiple columns keeping some columns static [BUMP]

Bump

 

[lramesh]

Re: combine multiple columns keeping some columns static [BUMP]

Bump.

 

[hiker95]

Re: combine multiple columns keeping some columns static [BUMP]

lramesh,

Your link is not working - please try again.