Sorting/rearranging columns with Index and Match

[Mr_Ragweed]

Hello, i think this should be simple but......i'm not great at this.

I have a single worksheet that looks like the following:

Customer	MasMas	MASMas#	MASChm	MASChm#	MASDry	MASDry#
tom	bob	2	bob	3	jimmy	2
bob	bill	1	bill	1	kevin	2
bill	jimmy	3	kevin	5	steve	3
kevin	steve	2			tom	1
steve
jimmy

<tbody>
</tbody>

I need to sort it based on column A and i ned the number rating to stay to the right of the name. As you can see the columns will be af varying length and some may have no data at all. The width of the sheet is fixed and ends with column X. The length of column A will be dynamic based on which location is running the information.
What it needs to lok like is this:

Customer	MasMas	MASMas#	MASChm	MASChm#	MASDry	MASDry#
tom					tom	1
bob	bob	2	bob	3
bill	bill	1	bill	1
kevin			kevin	5	kevin	2
steve	steve	2			steve	3
jimmy	jimmy	3			jimmy	2

<tbody>
</tbody>

Thanks in advance, and thanks again to those who have helped me before!

 

[hiker95]

Mr_Ragweed,

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

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that uses one array in memory to re-locate the names, and, numbers.

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

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer	MasMas	MASMas#	MASChm	MASChm#	MASDry	MASDry#
2	tom	bob	2	bob	3	jimmy	2
3	bob	bill	1	bill	1	kevin	2
4	bill	jimmy	3	kevin	5	steve	3
5	kevin	steve	2			tom	1
6	steve
7	jimmy
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer	MasMas	MASMas#	MASChm	MASChm#	MASDry	MASDry#
2	tom					tom	1
3	bob	bob	2	bob	3
4	bill	bill	1	bill	1
5	kevin			kevin	5	kevin	2
6	steve	steve	2			steve	3
7	jimmy	jimmy	3			jimmy	2
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.

Sub ReorgData()
' hiker95, 06/17/2015, ME862218
Dim lr As Long, lc As Long
Dim b As Variant, i As Long, c As Long
Dim n As Range, t As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  b = .Range(.Cells(1, 2), .Cells(lr, lc))
  .Range(.Cells(2, 2), .Cells(lr, lc)).ClearContents
  For i = 2 To UBound(b, 1)
    For c = 1 To UBound(b, 2) Step 2
      If b(i, c) <> "" Then
        Set n = .Columns(1).Find(b(i, c), LookAt:=xlWhole)
        Set t = .Rows(1).Find(b(1, c), LookAt:=xlWhole)
        If (Not n Is Nothing) * (Not t Is Nothing) Then
          .Cells(n.Row, t.Column) = b(i, c)
          .Cells(n.Row, t.Column + 1) = b(i, c + 1)
        End If
      End If
    Next c
  Next i
End With
Erase b
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.

 

[Mr_Ragweed]

thanks for the catch. Using Excel 2010 on PC

 

[hiker95]

Mr_Ragweed,

Have you tried the macro?

 

[Mr_Ragweed]

Tried it and i get a "type 13 mismatch" on the follwing bit:
If b(i, c) <> "" Then

I tried stepping through it and it looks like about the 3rd or 4th loop through after clearing the contents is when it errors out.
I'm not claiming to be very educated in this, but how is the data in columns B thru X being captured if we are clearing the contents of those cells? I see where we are looking and finding but it's unclear to me where we are re-pasting (for lack of a better term) ?
i'm not throwing rocks at you, i'm just trying to learn so i dont always have to ask.

Thank you for your help

 

[hiker95]

Mr_Ragweed,

I'm not claiming to be very educated in this, but how is the data in columns B thru X being captured if we are clearing the contents of those cells? I see where we are looking and finding but it's unclear to me where we are re-pasting (for lack of a better term) ?

The data to be sorted/moved/re-located is written to an array in memory. Then I go thru the array in memory, and, write the names, and, information, back to the correct row, and, column.

You can see by my screenshots, based on the raw data that you posted, that the macro works correctly.

Without not having a screenshot of your actual raw data going out to column X, it is difficult to say.

In order to continue, and, so that I can get it right on the next try, can we see your actual raw data workbook/worksheet.

The following is a free site.

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

 

[Mr_Ragweed]

Hiker -THANK YOU VER MUCH!! My column B was blank. I changed "step 2" to "Step 1" and now it seems to work. I'm going to try and throw a few more scenarios at it but "fingers crossed" i think we're there!
Thanks again.

 

[Mr_Ragweed]

...As an aside...It also helps not to have "#N/A" in any of the cells. But since this may be an option can i change the default "#N/A" to text so that it becomes acceptable?

 

[hiker95]

Hiker -THANK YOU VER MUCH!! My column B was blank. I changed "step 2" to "Step 1" and now it seems to work. I'm going to try and throw a few more scenarios at it but "fingers crossed" i think we're there!
Thanks again.

...As an aside...It also helps not to have "#N/A" in any of the cells. But since this may be an option can i change the default "#N/A" to text so that it becomes acceptable?

Mr_Ragweed,

It sounds like we have not see your actual raw data.

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

In order to continue, and, so that I can get it right on the next try, I would like to see your actual raw data workbook/worksheet(s), and, on another worksheet (manually formatted by you) with the results that you are looking for.

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.