Simple Row Alignment Question

[diemking]

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

 

[hiker95]

diemking,

Welcome to the MrExcel forum.

Part 1 of 3, first:

Sample raw data:

Excel 2007
	D	E
1	A	A1
2	B	B1
3		C1
4	C	D1
5		E1
6		F1
7
8	D
9
10	E
11	F
12
Sheet1

After the macro:

Excel 2007
	D	E
1	A	A1
2	B	B1
3
4	C	C1
5
6
7
8	D	D1
9
10	E	E1
11	F	F1
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.

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.

 

[hiker95]

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.

 

[diemking]

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 (-)

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Name 1[/TD]
[TD]Volume 1[/TD]
[TD]Name 2[/TD]
[TD]Volume 2[/TD]
[TD]Name 3[/TD]
[TD]Volume 3[/TD]
[TD]Name 4[/TD]
[TD]Volume 4[/TD]
[TD]Winning Name[/TD]
[TD]SLUGS[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]APL[/TD]
[TD]100[/TD]
[TD]Apples[/TD]
[TD]1000[/TD]
[TD]Appel[/TD]
[TD]10000[/TD]
[TD]Appel[/TD]
[TD]appel[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]500[/TD]
[TD]Ban[/TD]
[TD]480[/TD]
[TD]Ban ana[/TD]
[TD]800[/TD]
[TD]B-an Ana[/TD]
[TD]2000[/TD]
[TD]B-an Ana[/TD]
[TD]b-an-ana[/TD]
[/TR]
[TR]
[TD]Caramel[/TD]
[TD]300[/TD]
[TD]Charamel[/TD]
[TD]15[/TD]
[TD]Paramel[/TD]
[TD]25[/TD]
[TD]Karamel[/TD]
[TD]35[/TD]
[TD]Caramel[/TD]
[TD]caramel[/TD]
[/TR]
[TR]
[TD]Donkey[/TD]
[TD]0[/TD]
[TD]Don Key[/TD]
[TD]20[/TD]
[TD]Donk Ey[/TD]
[TD]100[/TD]
[TD]Donk[/TD]
[TD]50[/TD]
[TD]Donk Ey[/TD]
[TD]donk-ey[/TD]
[/TR]
[TR]
[TD]Eggplant[/TD]
[TD]200[/TD]
[TD]Egg pl ant[/TD]
[TD]1000[/TD]
[TD]Eggplants[/TD]
[TD]500[/TD]
[TD]Egg plants[/TD]
[TD]100[/TD]
[TD]Egg pl ant[/TD]
[TD]egg-pl-ant[/TD]
[/TR]
</tbody>[/TABLE]


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]

diemking,

Hi Hiker95, thanks for your swift and helpful reply!

You are very welcome. Grad I could help.

Be right back.

 

[hiker95]

diemking,

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

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Name 1	Volume 1	Name 2	Volume 2	Name 3	Volume 3	Name 4	Volume 4	Winning Name	SLUGS
2	Apple	10	APL	100	Apples	1000	Appel	10000
3	Banana	500	Ban	480	Ban ana	800	B-an Ana	2000
4	Caramel	300	Charamel	15	Paramel	25	Karamel	35
5	Donkey	0	Don Key	20	Donk Ey	100	Donk	50
6	Eggplant	200	Egg pl ant	1000	Eggplants	500	Egg plants	100
7
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Name 1	Volume 1	Name 2	Volume 2	Name 3	Volume 3	Name 4	Volume 4	Winning Name	SLUGS
2	Apple	10	APL	100	Apples	1000	Appel	10000	Appel	appel
3	Banana	500	Ban	480	Ban ana	800	B-an Ana	2000	B-an Ana	b-an-ana
4	Caramel	300	Charamel	15	Paramel	25	Karamel	35	Caramel	caramel
5	Donkey	0	Don Key	20	Donk Ey	100	Donk	50	Donk Ey	donk-ey
6	Eggplant	200	Egg pl ant	1000	Eggplants	500	Egg plants	100	Egg pl ant	egg-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).

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.