Align columns

G

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
L. Howard said:
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
Click to expand...
[TABLE="width: 1312"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1063"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]1410-0053 SLEEVE[/TD]
[TD="align: right"]$0.53[/TD]
[TD="align: right"]$2.60[/TD]
[TD] [/TD]
[TD]0100-1500C[/TD]
[TD]BODY ASSY[/TD]
[TD]$455.00[/TD]
[TD="align: right"]$179.73[/TD]
[TD="align: right"]199.69[/TD]
[TD="align: right"]224.66[/TD]
[TD="align: right"]561.64[/TD]
[/TR]
[TR]
[TD]0100-1540P PUMP HOUSING[/TD]
[TD="align: right"]$24.96[/TD]
[TD="align: right"]$79.50[/TD]
[TD][/TD]
[TD]0100-1500N[/TD]
[TD]BODY ASSY[/TD]
[TD]$1,044.00[/TD]
[TD="align: right"]$412.38[/TD]
[TD="align: right"]458.20[/TD]
[TD="align: right"]515.48[/TD]
[TD="align: right"]1288.69[/TD]
[/TR]
[TR]
[TD]0100-1543P HOUSING 3" 9343P[/TD]
[TD="align: right"]$25.55[/TD]
[TD="align: right"]$97.50[/TD]
[TD] [/TD]
[TD]0100-1500X[/TD]
[TD]BODY ASSY[/TD]
[TD]$1,446.00[/TD]
[TD="align: right"]$571.17[/TD]
[TD="align: right"]634.63[/TD]
[TD="align: right"]713.96[/TD]
[TD="align: right"]1784.91[/TD]
[/TR]
[TR]
[TD]0101-2100PM HEAD VERSA TWIN[/TD]
[TD="align: right"]$35.63[/TD]
[TD="align: right"]$95.00[/TD]
[TD] [/TD]
[TD]0100-1540P[/TD]
[TD]PUMP HOUSING[/TD]
[TD]$58.00[/TD]
[TD="align: right"]$22.91[/TD]
[TD="align: right"]25.46[/TD]
[TD="align: right"]28.64[/TD]
[TD="align: right"]71.59[/TD]
[/TR]
[TR]
[TD]0102-2300A CRANKCASE 2300[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$228.00[/TD]
[TD] [/TD]
[TD]0100-1543P[/TD]
[TD]PUMP HOUSING[/TD]
[TD]$171.00[/TD]
[TD="align: right"]$67.55[/TD]
[TD="align: right"]75.05[/TD]
[TD="align: right"]84.43[/TD]
[TD="align: right"]211.08[/TD]
[/TR]
[TR]
[TD]0113434308 CLIP COBRA 1" BLK[/TD]
[TD="align: right"]$1.97[/TD]
[TD="align: right"]$8.00[/TD]
[TD] [/TD]
[TD]0100-1700C[/TD]
[TD]BODY ASSY[/TD]
[TD]$246.00[/TD]
[TD="align: right"]$97.17[/TD]
[TD="align: right"]107.97[/TD]
[TD="align: right"]121.46[/TD]
[TD="align: right"]303.66[/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

W
  • Question Question
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
12
Views
168
rpaulson
rpaulson
R
  • Solved
Show most recent value within column (furthest row down)
2
Replies
16
Views
287
Peter_SSs
Peter_SSs
N
  • Question Question
Locate Cell and enter corresponding value into new cell
Replies
3
Views
71
myall_blues
myall_blues
C
  • Question Question
Transpose multiple columns to a single column
Replies
1
Views
93
Fluff
Fluff
B
  • Solved
VBA for Generating Cycle Counts
2
Replies
17
Views
633
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,315
Messages
6,177,845
Members
452,810
Latest member
jeffrey0409

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