Align columns

gregjp

New Member
Joined
Sep 3, 2014
Messages
3
Hello, I have a list of part numbers in column A with pricing in B & C, and a master list of part numbers in column D with other information in columns E-J. I'm trying to work out how to align A with D whilst retaining the relevant information.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Can you copy and post a small example of what you have, and below it show what it should look like when "aligned"?

Regards,
Howard
 
Upvote 0
Can you copy and post a small example of what you have, and below it show what it should look like when "aligned"?

Regards,
Howard

<colgroup><col><col span="2"><col><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
1410-0053 SLEEVE$0.53$2.60 0100-1500CBODY ASSY$455.00$179.73199.69224.66561.64
0100-1540P PUMP HOUSING$24.96$79.500100-1500NBODY ASSY$1,044.00$412.38458.20515.481288.69
0100-1543P HOUSING 3" 9343P$25.55$97.50 0100-1500XBODY ASSY$1,446.00$571.17634.63713.961784.91
0101-2100PM HEAD VERSA TWIN$35.63$95.00 0100-1540PPUMP HOUSING$58.00$22.9125.4628.6471.59
0102-2300A CRANKCASE 2300$0.00$228.00 0100-1543PPUMP HOUSING$171.00$67.5575.0584.43211.08
0113434308 CLIP COBRA 1" BLK$1.97$8.00 0100-1700CBODY ASSY$246.00$97.17107.97121.46303.66

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
gregjp,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


If I understand you correctly:


You want to go from this:


Excel 2007
ABCDEFGHIJ
11410-0053 SLEEVE$0.53$2.600100-1500CBODY ASSY$455.00$179.73199.69224.66561.64
20100-1540P PUMP HOUSING$24.96$79.500100-1500NBODY ASSY$1,044.00$412.38458.2515.481288.69
30100-1543P HOUSING 3" 9343P$25.55$97.500100-1500XBODY ASSY$1,446.00$571.17634.63713.961784.91
40101-2100PM HEAD VERSA TWIN$35.63$95.000100-1540PPUMP HOUSING$58.00$22.9125.4628.6471.59
50102-2300A CRANKCASE 2300$0.00$228.000100-1543PPUMP HOUSING$171.00$67.5575.0584.43211.08
60113434308 CLIP COBRA 1" BLK$1.97$8.000100-1700CBODY ASSY$246.00$97.17107.97121.46303
7
8
9
10
11
Sheet1


To this?


Excel 2007
ABCDEFGHIJ
10100-1500CBODY ASSY$455.00$179.73199.69224.66561.64
20100-1500NBODY ASSY$1,044.00$412.38458.2515.481288.69
30100-1500XBODY ASSY$1,446.00$571.17634.63713.961784.91
40100-1540P PUMP HOUSING$24.96$79.500100-1540PPUMP HOUSING$58.00$22.9125.4628.6471.59
50100-1543P HOUSING 3" 9343P$25.55$97.500100-1543PPUMP HOUSING$171.00$67.5575.0584.43211.08
60100-1700CBODY ASSY$246.00$97.17107.97121.46303
70101-2100PM HEAD VERSA TWIN$35.63$95.00
80102-2300A CRANKCASE 2300$0.00$228.00
90113434308 CLIP COBRA 1" BLK$1.97$8.00
101410-0053 SLEEVE$0.53$2.60
11
Sheet1



Are the above screenshots correct?
 
Upvote 0
gregjp,

If the above screenshots are correct:


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 AlignColumns()
' hiker95, 09/03/2014, ME802866
Dim r As Long, lr As Long, d As Range, s
Application.ScreenUpdating = False
Columns(1).Insert
lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("A1:A" & lr).NumberFormat = "@"
For Each d In Range("B1:B" & lr)
  s = Split(d, " ")
  d.Offset(, -1) = s(0)
Next d
Range("A1:D" & lr).Sort key1:=Range("A1"), order1:=1
Range("E1:K" & lr).Sort key1:=Range("E1"), order1:=1
Set d = Range("A1:A" & lr)
r = 1
Do While d.Cells(r, 1) <> ""
  If d.Cells(r, 1).Offset(, 4) <> "" Then
    If d.Cells(r, 1) < d.Cells(r, 1).Offset(, 4) Then
      d.Cells(r, 1).Offset(, 5).Resize(, 7).Insert -4121
    ElseIf d.Cells(r, 1) > d.Cells(r, 1).Offset(, 4) Then
      d.Cells(r, 1).Resize(, 4).Insert -4121
      lr = lr + 1
      Set d = Range("A1:A" & lr)
    End If
  End If
  r = r + 1
Loop
Columns(1).Delete
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 AlignColumns macro.



If the above screenshots are NOT correct, then I will check on your response later this afternoon.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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