Need help with obscure table reorganising

T

timmehhd

New Member
Joined
Mar 11, 2016
Messages
2
Hello I am having issues formatting the below within a macro , I tried using formulas but ran out of space, each 4422 beginning number can cover over 200 dates under it.

from this:



Excel 2007
DEFGHI
3Random TitleRandom TitleRandom TitleRandom TitleRandom TitleRandom Title
4442222442222thimmy the bestFort Thimmy12/02/2016
510/03/201600124521251251£112225.25 £2.25£350open
611/02/20160000215425421£1251.24 £0.00£555,444open
720/02/201200000005458569£6665.25 £25.25£98.65closed
8442268542411Sam is goodPalace the best13/02/2016
922/01/201612521021002010£112225.25 £2.25£555,444closed
1010/01/201000121202020125£1251.24 £0.00£98.65open
1101/01/201300000005458570£112225.25 £2.25£555,444open
1222/01/201600000005458589£1251.24 £0.00£98.65open
1310/01/201011241251252362£6665.25 25.25£88,872.25closed
Sheet1
Cell Formulas
RangeFormula
D3Random Title
D4442222442222
D542439
D642411
D740959
D8442268542411
D942391
D1040188
D1141275
D1242391
D1340188
E3Random Title
E4thimmy th
E500124521251251
E60000215425421
E700000005458569
E8Sam is go
E912521021002010
E1000121202020125
E1100000005458570
E1200000005458589
E1311241251252362
F3Random Title
F4e best
F8od
G3Random Title
G4Fort Thimmy
G5£112225.25 £2.25
G6£1251.24 £0.00
G7£6665.25 £25.25
G8Palace the best
G9£112225.25 £2.25
G10£1251.24 £0.00
G11£112225.25 £2.25
G12£1251.24 £0.00
G13£6665.25 25.25
H3Random Title
H442412
H5350
H6555444
H798.65
H842413
H9555444
H1098.65
H11555444
H1298.65
H1388872.25
I3Random Title
I5open
I6open
I7closed
I9closed
I10open
I11open
I12open
I13closed



to this


Excel 2007
DEFGHIJ
18Fort Thimmy12/02/2016442222442222 thimmy the best10/03/201600124521251251£112225.25 £2.25£350open
19Fort Thimmy12/02/2016442222442222 thimmy the best11/02/20160000215425421£1251.24 £0.00£555,444open
20Fort Thimmy12/02/2016442222442222 thimmy the best20/02/201200000005458569£6665.25 £25.25£98.65closed
21Palace the best13/02/2016442268542411 Sam is good22/01/201612521021002010£112225.25 £2.25£555,444closed
22Palace the best13/02/2016442268542411 Sam is good10/01/201000121202020125£1251.24 £0.00£98.65open
23Palace the best13/02/2016442268542411 Sam is good01/01/201300000005458570£112225.25 £2.25£555,444open
24Palace the best13/02/2016442268542411 Sam is good22/01/201600000005458589£1251.24 £0.00£98.65open
25Palace the best13/02/2016442268542411 Sam is good10/01/201011241251252362£6665.25 25.25£88,872.25closed
Sheet1
Cell Formulas
RangeFormula
D18Fort Thimmy12/02/2016
D19Fort Thimmy12/02/2016
D20Fort Thimmy12/02/2016
D21Palace the best13/02/2016
D22Palace the best13/02/2016
D23Palace the best13/02/2016
D24Palace the best13/02/2016
D25Palace the best13/02/2016
E18442222442222 thimmy the best
E19442222442222 thimmy the best
E20442222442222 thimmy the best
E21442268542411 Sam is good
E22442268542411 Sam is good
E23442268542411 Sam is good
E24442268542411 Sam is good
E25442268542411 Sam is good
F1842439
F1942411
F2040959
F2142391
F2240188
F2341275
F2442391
F2540188
G1800124521251251
G190000215425421
G2000000005458569
G2112521021002010
G2200121202020125
G2300000005458570
G2400000005458589
G2511241251252362
H18£112225.25 £2.25
H19£1251.24 £0.00
H20£6665.25 £25.25
H21£112225.25 £2.25
H22£1251.24 £0.00
H23£112225.25 £2.25
H24£1251.24 £0.00
H25£6665.25 25.25
I18350
I19555444
I2098.65
I21555444
I2298.65
I23555444
I2498.65
I2588872.25
J18open
J19open
J20closed
J21closed
J22open
J23open
J24open
J25closed



I know its a strange request but the rest of the macro is set up to accept this data , I would want it to be put in the same locations on a different sheet, for instance the initial data is on D:I and the converted data i would like to be on A B (leave C blank) and then D:I. (if that makes any sense) I will be very grateful for the help!
 
Sort by date Sort by votes
Welcome to the MrExcel board!

See if this gets anywhere near the requirement.
Test in a copy of your workbook.

Assumes:
- Data is on 'Sheet1' with first actual data cell of D4
- Results go on to Sheet2
- Sheet2 already exists in the workbook and any data already on it can be deleted.

Rich (BB code): 
Sub RearrangeData()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim s1 As String, s2 As String
  
  With Sheets("Sheet1")
    a = .Range("D4", .Range("D" & .Rows.Count).End(xlUp)).Resize(, 6).Value
  End With
  ReDim b(1 To UBound(a), 1 To 7)
  For i = 1 To UBound(a)
    If IsDate(a(i, 1)) Then
      k = k + 1
      b(k, 1) = s1: b(k, 2) = s2: b(k, 3) = a(i, 1): b(k, 4) = a(i, 2): b(k, 5) = a(i, 4): b(k, 6) = a(i, 5): b(k, 7) = a(i, 6)
    Else
      s1 = a(i, 4) & Format(a(i, 5), "dd/mm/yyyy")
      s2 = a(i, 1) & Space(15) & a(i, 2) & a(i, 3)
    End If
  Next i
  With Sheets("Sheet2")
    .UsedRange.ClearContents
    .Columns("A:G").NumberFormat = "General"
    .Columns("D").NumberFormat = "0"
    .Range("A1").Resize(k, 7).Value = b
    .Columns("C").Insert
    .Columns("F").Insert
    .Columns("A:I").AutoFit
  End With
End Sub


Original data:

Excel Workbook
DEFGHI
3Random TitleRandom TitleRandom TitleRandom TitleRandom TitleRandom Title
4442222442222thimmy the bestFort Thimmy12/02/2016
510/3/2016124521251251112225.25 2.25350open
611/2/20162154254211251.24 0.00555,444open
720/2/201254585696665.25 25.2598.65closed
8442268542411Sam is goodPalace the best13/02/2016
922/1/201612521021002010112225.25 2.25555,444closed
1010/1/20101212020201251251.24 0.0098.65open
111/1/20135458570112225.25 2.25555,444open
1222/1/201654585891251.24 0.0098.65open
1310/1/2010112412512523626665.25 25.2588,872.25closed
Sheet1




Results:

Excel Workbook
ABCDEFGHI
1Fort Thimmy12/02/2016442222442222 thimmy the best10/3/2016124521251251112225.25 2.25350open
2Fort Thimmy12/02/2016442222442222 thimmy the best11/2/20162154254211251.24 0.00555,444open
3Fort Thimmy12/02/2016442222442222 thimmy the best20/2/201254585696665.25 25.2598.65closed
4Palace the best13/02/2016442268542411 Sam is good22/1/201612521021002010112225.25 2.25555,444closed
5Palace the best13/02/2016442268542411 Sam is good10/1/20101212020201251251.24 0.0098.65open
6Palace the best13/02/2016442268542411 Sam is good1/1/20135458570112225.25 2.25555,444open
7Palace the best13/02/2016442268542411 Sam is good22/1/201654585891251.24 0.0098.65open
8Palace the best13/02/2016442268542411 Sam is good10/1/2010112412512523626665.25 25.2588,872.25closed
Sheet2
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel VBA for Data Handling
Replies
1
Views
110
MikeVol
MikeVol
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
761
mumps
mumps
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
404
dilshod_k
D
GMC The Macro Man
  • Question Question
Clear Data without VBA
2
Replies
12
Views
4K
mumps
mumps
S
  • Question Question
How to create a proper excel table from unorganized data?
Replies
5
Views
531
Burrgogi
Burrgogi

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