Sorting data to illustrate infrastructure work in Visio plans

[Lisatrold]

Hi Everybody

I've tried to search for a related topic, but didn't find any. I apologize if I should have posted this question in a related thread.

I need to sort specific data, so I can link it to a plan I've drawn in MS Visio. The data is railway stations, turnouts and the number of maintenance orders represented by an unique order id.

I dont speak "Excel" very good, and need help to get my data from this to this automatic (I have a vast amount of data).

Thanks in advance, Lisa

 

[hiker95]

Lisatrold,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


In the future, I would suggest that you not post a picture/graphic.

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here



Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Geo	Geo Place	Order
2	KH	011000-SPOR1	10020660
3	KH	011000-SPOR1	10038885
4	KH	011000-SPOR1	10038886
5	KH	011000-SPOR1	10038887
6	KH	011000-SPOR2	10040642
7	KH	011000-SPOR22	10035842
8	KH	011000-SPOR22	10036772
9
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Geo	Geo Place	Order		Geo	Geo Place	Order 1	Order 2	Order 3	Order 4
2	KH	011000-SPOR1	10020660		KH	011000-SPOR1	10020660	10038885	10038886	10038887
3	KH	011000-SPOR1	10038885		KH	011000-SPOR2	10040642
4	KH	011000-SPOR1	10038886		KH	011000-SPOR22	10035842	10036772
5	KH	011000-SPOR1	10038887
6	KH	011000-SPOR2	10040642
7	KH	011000-SPOR22	10035842
8	KH	011000-SPOR22	10036772
9
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.

Sub ReorgData()
' hiker95, 10/05/2014, ME809726
Dim r As Long, lr As Long, nr As Long, n As Long, maxn As Long, luc As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:C" & lr).Sort key1:=.Range("A2"), order1:=1, key2:=.Range("B2"), order2:=1, key3:=.Range("C2"), order3:=1
  .Columns(4).ClearContents
  With .Range("D2:D" & lr)
    .FormulaR1C1 = "=RC[-3]&RC[-2]"
    .Value = .Value
  End With
  .Cells(1, 5).Resize(, 2).Value = .Cells(1, 1).Resize(, 2).Value
  .Cells(1, 5).Resize(, 2).Font.Bold = True
  nr = 1
  For r = 2 To lr
    n = Application.CountIf(.Columns(4), .Cells(r, 4).Value)
    If n > maxn Then maxn = n
    nr = nr + 1
    .Cells(nr, 5).Resize(, 2).Value = .Cells(r, 1).Resize(, 2).Value
    If n = 1 Then
      .Cells(nr, 7) = .Range("C" & r).Value
    ElseIf n > 1 Then
      .Cells(nr, 7).Resize(, n) = Application.Transpose(.Range("C" & r & ":C" & r + n - 1).Value)
    End If
    r = r + n - 1
  Next r
  .Columns(4).ClearContents
  luc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With .Range(.Cells(1, 7), .Cells(1, luc))
    .Formula = "=""Order "" & COLUMN() - 6"
    .Value = .Value
    .Font.Bold = True
  End With
  .UsedRange.Columns.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

Then run the ReorgData macro.

 

[Lisatrold]

Hello hiker95

The macro worked perfectly (on my PC with 2013 pro), and what an easy instruction. Thanks a million muffins! Next time an Excel challenge arises, I will surely return. And use proper forum standards

Many greetings from ExcelonthecouchSunday-Lisa

 

[hiker95]

Lisatrold,

Thanks for the feedback, and, picture.

You are very welcome. Glad I could help.

And, come back anytime.