Simple Row Alignment Question

diemking

New Member
I looked throughout the forums and could not find a formula to my (much easier to solve) problem:

Basically I have two columns of data, D and E
Because column D has a varying number of spaces between each row and because I ran D through some program that crunched out column E and deleted the spaces I got something like this: (where * = a space)

A A1
B B1
* C1
C D1
* E1
* F1
* *
D *
* *
E *
F *

I want the first column to retain its order and spacing between its rows but I want the second column to match up and align with the first column so that:

A A1
B B1
* *
C C1
* *
* *
* *
D D1
* *
E E1
F F1

How can I do this?

Also second, harder problem:

1) The spreadsheet goes like this~

(Column 1) 1, (Column 2) 1:values, (Column 3) 2, (Column 4) 2:values, (Column 5) 3, (Column 6) 3:values, (Column 7) 4, (Column 8) 4:values,

Is there any code out there that would allow me to select the input (1, 2, 3, or 4) with the highest value for Column 9?

Thirdly (also hard problem)

2) Is there any code out there that would allow me to make a new Column 10 out of Column 9 by transferring the spaces and hyphens in Column 9 into hyphens and turning all the letters lower-cased?

Any suggestions?
At the very least can you help me with the verrrrry first matching formula?

Thank you so much!
Vinson

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

hiker95

Well-known Member
diemking,

Welcome to the MrExcel forum.

Part 1 of 3, first:

Sample raw data:

Excel 2007
DE
1AA1
2BB1
3C1
4CD1
5E1
6F1
7
8D
9
10E
11F
12
Sheet1

After the macro:

Excel 2007
DE
1AA1
2BB1
3
4CC1
5
6
7
8DD1
9
10EE1
11FF1
12
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, by highlighting the code and pressing the keys CTRL + C
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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
``````Option Explicit
Sub AlignEtoD()
' hiker95, 03/28/2013
' http://www.mrexcel.com/forum/excel-questions/694013-simple-row-alignment-question.html
Dim e As Variant, i As Long, lr As Long, fr As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row
e = Range("E1:E" & lr)
Range("E1:E" & lr).ClearContents
For i = LBound(e, 1) To UBound(e, 1)
fr = 0
On Error Resume Next
fr = Application.Match(Left(e(i, 1), 1), Columns(4), 0)
On Error GoTo 0
If fr > 0 Then
Cells(fr, 5) = e(i, 1)
End If
Next i
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 AlignEtoD macro.

Last edited:

hiker95

Well-known Member
diemking,

For Parts 2 and 3 of 3:

I will need to see screenshots of each part before and after. The after screenshot will be manually formatted by you for the results you are looking for.

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

Excel Jeanie
MrExcel HTML Maker

If you are not able to give us screenshots:
sensitive data scrubbed/removed/changed
mark the workbook for sharing

diemking

New Member

For part 1, what I did instead was just organize the Column D by alphabetical order, thereby eliminating the spaces and easily copying and pasting the list to match Column D on Column E.

This is more or less what I want Part 2 and Part 3 to be like:

Part 2 (Winning Name): Is the Name with the highest Volume. For example, in Row Two, the Name with the highest Volume is Appel with 10000 in volume; therefore the Winning Name is Appel.

Part 3 (SLUGS): Is the "Winning Name" in lower-case letters and with spaces replaced by hyphens (-)

 Name 1 Volume 1 Name 2 Volume 2 Name 3 Volume 3 Name 4 Volume 4 Winning Name SLUGS Apple 10 APL 100 Apples 1000 Appel 10000 Appel appel Banana 500 Ban 480 Ban ana 800 B-an Ana 2000 B-an Ana b-an-ana Caramel 300 Charamel 15 Paramel 25 Karamel 35 Caramel caramel Donkey 0 Don Key 20 Donk Ey 100 Donk 50 Donk Ey donk-ey Eggplant 200 Egg pl ant 1000 Eggplants 500 Egg plants 100 Egg pl ant egg-pl-ant

<tbody>
</tbody>

Is there any way that I can go about finding the winning name and slug columns without manually going through thousands of rows?

Also, it doesn't have to be a formula, just so long as I can easily input the winning name and slugs.
For example: for part 1, I didn't use a formula, instead just arranging a list in alphabetical order to get rid of the spaces and easily copy paste a list in an adjacent column matching and corresponding with the individual cells in the initial (now alphabetized) column.

Thanks so much! (I'm pretty new with excel). Please let me know if you have any further questions!

hiker95

Well-known Member
diemking,

You are very welcome. Grad I could help.

Be right back.

hiker95

Well-known Member
diemking,

Sample raw data in worksheet Sheet1, beginning in cell A1:

Excel 2007
ABCDEFGHIJ
1Name 1Volume 1Name 2Volume 2Name 3Volume 3Name 4Volume 4Winning NameSLUGS
2Apple10APL100Apples1000Appel10000
3Banana500Ban480Ban ana800B-an Ana2000
4Caramel300Charamel15Paramel25Karamel35
5Donkey0Don Key20Donk Ey100Donk50
6Eggplant200Egg pl ant1000Eggplants500Egg plants100
7
Sheet1

After the macro:

Excel 2007
ABCDEFGHIJ
1Name 1Volume 1Name 2Volume 2Name 3Volume 3Name 4Volume 4Winning NameSLUGS
2Apple10APL100Apples1000Appel10000Appelappel
3Banana500Ban480Ban ana800B-an Ana2000B-an Anab-an-ana
4Caramel300Charamel15Paramel25Karamel35Caramelcaramel
5Donkey0Don Key20Donk Ey100Donk50Donk Eydonk-ey
6Eggplant200Egg pl ant1000Eggplants500Egg plants100Egg pl antegg-pl-ant
7
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).

Code:
``````Option Explicit
Sub FindWinniningNameSLUGS()
' hiker95, 04/01/2013
' http://www.mrexcel.com/forum/excel-questions/694013-simple-row-alignment-question.html
Dim a As Variant
Dim i As Long, ii As Long, mv As Long, mn As String
With Sheets("Sheet1")
a = .Cells(1).CurrentRegion
For i = 2 To UBound(a, 1)
mv = 0: mn = ""
For ii = 2 To 8 Step 2
If a(i, ii) > mv Then
mv = a(i, ii)
mn = a(i, ii - 1)
End If
Next ii
a(i, 9) = mn
mn = Replace(mn, " ", "-")
a(i, 10) = LCase(mn)
Next i
.Cells(1).CurrentRegion = a
.Columns.AutoFit
End With
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 FindWinniningNameSLUGS macro.

Replies
4
Views
316
Replies
5
Views
221
Replies
10
Views
375
Replies
10
Views
150
Replies
5
Views
248

1,195,994
Messages
6,012,750
Members
441,724
Latest member
Aalbid

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.

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

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