"Complex"Data Rearrange

[WIbptr1]

Hello. I and my colleagues like Excel and so we would like to do this task in Excel. We need to rearrange the data in the first table to that in the second table (attachment). We appreciate your advice.

 

[Somendra Misra]

Hi,

See the attached file using two helper columns highlighted in Yellow color. This is working for your sample file.

Regards,

 

[p45cal]

You can do this manually with 1 copy/paste procedure and 1 delete.
Assuming your data is laid out as in Somendra's file above, you would select the following range of cells:
A2:C3
Now, while holding down the CTRL key, further select range F2:G3.
It should look like:

On the keyboard press Ctrl + c (or click Copy on the Clipboard section of the Home tab of the ribbon).
Now select cell A4 and press Ctrl + v (or press Paste in the ribbon).
Now you can delete columns F and G.
QED.

 

[WIbptr1]

You can do this manually with 1 copy/paste procedure and 1 delete.
Assuming your data is laid out as in Somendra's file above, you would select the following range of cells:
A2:C3
Now, while holding down the CTRL key, further select range F2:G3.
It should look like:
View attachment 32973
On the keyboard press Ctrl + c (or click Copy on the Clipboard section of the Home tab of the ribbon).
Now select cell A4 and press Ctrl + v (or press Paste in the ribbon).
Now you can delete columns F and G.
QED.

Somendra and p45cal,
I will bring both of your suggestions to my colleagues and we'll select the one that works best for us. Many thanks!

 

[p45cal]

cross posted http://www.excelguru.ca/forums/showthread.php?6422-quot-Complex-quot-Data-Rearrange

 

[p45cal]

I made a mistake with which columns to copy in my last post, it should be:
You would first select the following range of cells:
A2:C3
Then, while holding down the CTRL key, further select range E2:E3 and G2:G3.
It should look like:


On the keyboard press Ctrl + c (or click Copy on the Clipboard section of the Home tab of the ribbon).
Now select cell A4 and press Ctrl + v (or press Paste in the ribbon).
Now you can delete columns E and G.

I've put this into a macro and added a sort routine:

With ActiveSheet
  Set RngToCopy = Intersect(.Range("A:C,E:E,G:G"), .Range("A1").CurrentRegion.Resize(.Range("A1").CurrentRegion.Rows.Count - 1).Offset(1))
  RngToCopy.Copy RngToCopy.Cells(1).Offset(RngToCopy.Rows.Count)
  Union(RngToCopy.Areas(2).Offset(-1).Resize(RngToCopy.Rows.Count + 1), RngToCopy.Areas(3).Offset(-1).Resize(RngToCopy.Rows.Count + 1)).Delete Shift:=xlToLeft
  .Range("D1").Value = "Objective Type"
  .Range("E1").Value = "Objective Result"
  With .Sort
  .SortFields.Clear
  .SortFields.Add Key:=ActiveSheet.Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=ActiveSheet.Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SetRange ActiveSheet.Range("A1").CurrentRegion
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
End With
End Sub

 

[Chihiro]

If you have access to PowerQuery (Get & Transform in Excel 2016). Following method can be used as well.

1. Convert data into Excel Table format.

2. Go to "Get & Transform" found in Data ribbon tab and select "New Query"->"From File"->"From Workbook"

3. Choose the file with table/data (could be same workbook as where query is being added).

4. Select the table created in step 1 and click on Edit.

5. Remove Objective 2 Type & Objective 2 Result and rename Objective 1 Type & Objective 1 Result to Objective Type and Objective Result

6. Select "Close & Load To" and check Only Create Connection

7. Repeat step 1~3, but in step 4, remove Objective 1 Type & Objective 1 Result this time and rename to same as previous connection and Only Create Connection.

8. Click on Show Queries found in "Get & Transform" and right click on 1st query.

9. From drop down, pick "Append" and add 2nd query to the 1st. This time, choose "Close & Load" and new sheet will be created with result.

One advantage to this method is that as long as source table structure remains consistent, it will dynamically add new rows by refreshing table (as long as you saved after updating the source table).

See attached sample.

EDIT: It's actually easier to use "From Table" instead of "New Query" if you are referencing table within same workbook. This will keep source as CurrentWorkbook (instead of Excel.Workbook, where it requires path & filename string).

 

[Chihiro]

Here's another version.

• Uses Sheet1 as source from same workbook
• Sheet1, Cell K1 has been turned into Named Range (which stores path & file name)
  • Contains formula: =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]Sheet1","")
• Sheet1 & Sheet1(2) query has been edited in Query Editor's Advanced Editor to modify PowerQuery formula

Added following just below "let" to take path & file name from Named Range
path = Excel.CurrentWorkbook(){[Name="fpath"]}[Content]{0}[Column1],

Then Source line has been edited. "C:\Path\filname" has been replaced with path.

Source = Excel.Workbook(File.Contents(path), null, true),

This makes it dynamically look for file's location without using table as data source.