Moving Data in Cell

D

daigia_dzadzich

New Member
Joined
Jan 5, 2016
Messages
3
Hi,

I'm currently using excel 2010 & possibly upgrade to a later version in a near future. My question is that I currently have a large excel sheet with data look like below (Please note that the common fields are the member id & dependent associate id):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]member Id[/TD]
[TD]name[/TD]
[TD]dependent associate id[/TD]
[TD]dependent name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jim[/TD]
[TD]1[/TD]
[TD]Carol[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Todd[/TD]
[TD]2[/TD]
[TD]Sue[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chris[/TD]
[TD]3[/TD]
[TD]Kristy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Aiden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Isabella[/TD]
[/TR]
</tbody>[/TABLE]












What formula & syntax to move the data which have the end result looking like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]member id[/TD]
[TD]name[/TD]
[TD]dependent name 1[/TD]
[TD]dependent name 2[/TD]
[TD]dependent name 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jim[/TD]
[TD]Carol[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Todd[/TD]
[TD]Sue[/TD]
[TD]Sam[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chris[/TD]
[TD]Kristy[/TD]
[TD]Aiden[/TD]
[TD]Isabella[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Could you please help? Appreciate any inputs/assistants.

Thank you in advance,
Chris
 
Sort by date Sort by votes
daigia_dzadzich,

Welcome to the MrExcel forum.

1. What version of Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider, based on the fact that your raw data is sorted/grouped in column D.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:


Excel 2007
ABCDEFGHIJKL
1member Idnamedependent associate iddependent namemember IDnamedependent name 1dependent name 2dependent name 3
21Jim1Carol1JimCarolN/AN/A
32Todd2Sue2ToddSueSamN/A
42Sam3ChrisKristyAidenIsabella
53Chris3Kristy
63Aiden
73Isabella
8
Sheet1


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.

Code: 
Sub ReorgData()
' hiker95, 01/05/2016, ME912210
Dim lr As Long, r As Long, nr As Long, nc As Long, lc As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Range("G1").Resize(, 2).Value = Array("member ID", "name")
  nr = 2: nc = 9
  lr = .Cells(Rows.Count, 3).End(xlUp).Row
  For r = 2 To lr
    n = Application.CountIf(.Columns(3), .Cells(r, 3).Value)
    If n = 1 Then
      .Cells(nr, 7).Resize(, 2).Value = .Cells(r, 1).Resize(, 2).Value
      .Cells(nr, nc).Value = .Cells(r, 4).Value
    Else
      .Cells(nr, 7).Resize(, 2).Value = .Cells(r, 1).Resize(r, 1).Resize(, 2).Value
      .Cells(nr, nc).Resize(, n).Value = Application.Transpose(.Range(.Cells(r, 4), .Cells(r + n - 1, 4)).Value)
    End If
    nr = nr + 1
    r = r + n - 1
  Next r
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With .Cells(1, nc).Resize(, lc - 8)
    .Formula = "=""dependent name "" & Column() - 8"
    .Value = .Value
  End With
  On Error Resume Next
  .Range(.Cells(2, nc), .Cells(nr - 1, lc)).SpecialCells(xlCellTypeBlanks).Value = "N/A"
  .Columns(1).Resize(, lc).AutoFit
End With
Application.ScreenUpdating = True
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Last edited:
Upvote 0
hiker95 said:
daigia_dzadzich,

Welcome to the MrExcel forum.

1. What version of Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider, based on the fact that your raw data is sorted/grouped in column D.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:

Excel 2007
ABCDEFGHIJKL

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]member Id[/TD]
[TD="bgcolor: #FFFFFF"]name[/TD]
[TD="bgcolor: #FFFFFF"]dependent associate id[/TD]
[TD="bgcolor: #FFFFFF"]dependent name[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]member ID[/TD]
[TD="bgcolor: #FFFFFF"]name[/TD]
[TD="bgcolor: #FFFFFF"]dependent name 1[/TD]
[TD="bgcolor: #FFFFFF"]dependent name 2[/TD]
[TD="bgcolor: #FFFFFF"]dependent name 3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF"]Carol[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]Carol[/TD]
[TD="bgcolor: #FFFFFF"]N/A[/TD]
[TD="bgcolor: #FFFFFF"]N/A[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]Todd[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]Sue[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]Todd[/TD]
[TD="bgcolor: #FFFFFF"]Sue[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="bgcolor: #FFFFFF"]N/A[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]Chris[/TD]
[TD="bgcolor: #FFFFFF"]Kristy[/TD]
[TD="bgcolor: #FFFFFF"]Aiden[/TD]
[TD="bgcolor: #FFFFFF"]Isabella[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]Chris[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]Kristy[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]Aiden[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]Isabella[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



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.

Code: 
Sub ReorgData()
' hiker95, 01/05/2016, ME912210
Dim lr As Long, r As Long, nr As Long, nc As Long, lc As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Range("G1").Resize(, 2).Value = Array("member ID", "name")
  nr = 2: nc = 9
  lr = .Cells(Rows.Count, 3).End(xlUp).Row
  For r = 2 To lr
    n = Application.CountIf(.Columns(3), .Cells(r, 3).Value)
    If n = 1 Then
      .Cells(nr, 7).Resize(, 2).Value = .Cells(r, 1).Resize(, 2).Value
      .Cells(nr, nc).Value = .Cells(r, 4).Value
    Else
      .Cells(nr, 7).Resize(, 2).Value = .Cells(r, 1).Resize(r, 1).Resize(, 2).Value
      .Cells(nr, nc).Resize(, n).Value = Application.Transpose(.Range(.Cells(r, 4), .Cells(r + n - 1, 4)).Value)
    End If
    nr = nr + 1
    r = r + n - 1
  Next r
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With .Cells(1, nc).Resize(, lc - 8)
    .Formula = "=""dependent name "" & Column() - 8"
    .Value = .Value
  End With
  On Error Resume Next
  .Range(.Cells(2, nc), .Cells(nr - 1, lc)).SpecialCells(xlCellTypeBlanks).Value = "N/A"
  .Columns(1).Resize(, lc).AutoFit
End With
Application.ScreenUpdating = True
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
Click to expand...

Thank you, Hiker95 for your feedback. I'm using Windows 7 & not familiar w/ the macro... sorry :-(
I'm looking for a formula to enter into a cell & then drag it to other cells.

Thanks.
 
Upvote 0
Thank you, Hiker95 for your feedback. I'm using Windows 7 & not familiar w/ the macro... sorry
Click to expand...

daigia_dzadzich,

Thanks for the feedback. You are welcome.

I'm looking for a formula to enter into a cell & then drag it to other cells.
Click to expand...

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Excel VBA: Form to be filled in with data from another worksheet
2
Replies
15
Views
742
MikeVol
MikeVol
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
696
Welshy1491
W
M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
489
MrsGrayMarlin
M
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
618
Ol Reliable
O
I
  • Question Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
966
ItsNursinTime
I

Forum statistics

Threads
1,226,894
Messages
6,193,541
Members
453,807
Latest member
PKruger

Share this page

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
Back
Top