Simple Row Alignment Question

diemking

New Member
Joined
Mar 28, 2013
Messages
4
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
2. Open your 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 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:
Upvote 0
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?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Hi Hiker95, thanks for your swift and helpful reply!

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 1Volume 1Name 2Volume 2Name 3Volume 3Name 4Volume 4Winning NameSLUGS
Apple10APL100Apples1000Appel10000Appelappel
Banana500Ban480Ban ana800B-an Ana2000B-an Anab-an-ana
Caramel300Charamel15Paramel25Karamel35Caramelcaramel
Donkey0Don Key20Donk Ey100Donk50Donk Eydonk-ey
Eggplant200Egg pl ant1000Eggplants500Egg plants100Egg pl antegg-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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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