Alphabetical Order with Concatenated Strings

jgolvach

New Member
Joined
Sep 23, 2010
Messages
38
Howdy,

I can get this to work quite right. I have built a little system in vba so that I can put in a first name and last name and some data on an "data entry sheet". Then I hit a submit button which creates and entry on the next sheet (the "records sheet"). The records sheet will have hundreds of entries, one for each person, with each entry consisting of about 10 rows to hold the name, some contact information, and that persons data.

The trick is, i need new entries to post to the data sheet in alphabetical order based on a label in column a for each entry. The labels are concatenated strings combining the last name, a comma, then the first name (typical "last name, first name" format: Doe, John). I am comparing the concatenated label on the data entry page ("chknm") with the concatenated labels on the records page ("lstnm") to determine where the new entry should be inserted (row x). here is an example that shows how i tried to do it...

****************************
dim chknm as string
dim lstnm as string
dim irow as long
dim x as long

sheets("data entry").select
chknm = range("B3").value
rows("1:10").select.
selection.copy

sheets("records sheet").
for irow 1 to totalrows
lstnm = cells(irow, 1).value
if chknm > lstnm then goto nextirow
if chknm < lstnm then
x = irow
else
msgbox ("This person has already been entered")
goto cancelentry
end if
nextirow:
next irow

foundx:
cells(x, 1).entirerow.insert shfit:=xldown

cancelentry:
endsub
*******************************

i dont know what vba is comparing when it performs the if chnm < lstnm line of code... if anyone knows where i am going wrong or can explain what this is actually comparing between the two strings... or can suggest another way to do this entirely, i would really apprecaite it as i have been stuck on this for several days.


Thanks!!!
Jake
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jgolvach

New Member
Joined
Sep 23, 2010
Messages
38
Sorry it took me so long to respond. Here is the code as I have it so far. It is a nightmare and there are several variables that I have declared but did not end up using. Just a heads up...

Private Sub CommandButton3_Click()
Dim x As Long
Dim y As Long
Dim z As Long
Dim q As Long
Dim b As Long
Dim n As Long
Dim IntRow As Long
Dim allrows As Long
Dim LBRow As Long
Dim UBRow As Long
Dim Arow As Long
Dim irow As Long
Dim lstnmrow As Long
Dim chkfstnm As String
Dim chklstnm As String
Dim lstnm As String
Dim fstnm As String
Dim LBN As String
Dim UBN As String

Application.ScreenUpdating = False

'find us an x row
Sheets("Interests By Owners").Select
With Sheets("Interests By Owners")
If Range("D32").Value = "" Then
x = 28
Else
'find x by alphabetic order
Sheets("Data Input").Select
chklstnm = Sheets("Data Input").Range("I7").Value
chkfstnm = Sheets("Data Input").Range("D7").Value

Sheets("Interests By Owners").Select
z = Range("C1000000").End(xlUp).Row
lstnm = Cells(z + 5, 5).Value
fstnm = Cells(z + 4, 5).Value
If chklstnm > lstnm Then
x = Range("D1000000").End(xlUp).Row + 20
Else
For Arow = 27 To z
If Sheets("Interets By Ownwers").Cells(Arow, 3).Value = "" Then GoTo NextArow
fstnm = Sheets("Interets By Ownwers").Cells(Arow + 4, 5).Value
If chkfstnm > fstnm Then GoTo NextArow
If chkfstnm < fstnm Then
x = Arow
Else
If chkfstnm = fstnm Then
MsgBox ("This dude already exists!")
Else
End If
End If
NextArow:
Next Arow
End If

Sheets("Interests By Owners").Rows("6:25").Select
Selection.Copy
Sheets("Interests By Owners").Cells(x, 1).EntireRow.Insert shift:=xlDown

GoTo HERE
End If

Sheets("Interests By Owners").Rows("6:25").Select
Selection.Copy
Sheets("Interests By Owners").Cells(x, 1).EntireRow.Insert shift:=xlDown

HERE:
q = x + 19
Sheets("Interests By Owners").Rows(x & ":" & q).Hidden = False
End With

'get input date (todays date)
Sheets("Interests By Owners").Select
Sheets("Interests By Owners").Cells(x + 5, 12).Value = Sheets("Data Input").Range("AC3").Value

'Get Contact's Info
Sheets("Interests By Owners").Select
Sheets("Interests By Owners").Cells(x + 4, 5).Value = Sheets("Data Input").Range("D7").Value
Sheets("Interests By Owners").Cells(x + 5, 5).Value = Sheets("Data Input").Range("I7").Value
Sheets("Interests By Owners").Cells(x + 6, 5).Value = Sheets("Data Input").Range("D9").Value
Sheets("Interests By Owners").Cells(x + 7, 5).Value = Sheets("Data Input").Range("I9").Value
Sheets("Interests By Owners").Cells(x + 8, 5).Value = Sheets("Data Input").Range("K9").Value
Sheets("Interests By Owners").Cells(x + 9, 5).Value = Sheets("Data Input").Range("L9").Value
Sheets("Interests By Owners").Cells(x + 8, 7).Value = Sheets("Data Input").Range("D11").Value
Sheets("Interests By Owners").Cells(x + 9, 7).Value = Sheets("Data Input").Range("I11").Value
Sheets("Interests By Owners").Cells(x + 10, 5).Value = Sheets("Data Input").Range("D13").Value
Sheets("Interests By Owners").Cells(x + 10, 7).Value = Sheets("Data Input").Range("I13").Value

'Get Owner's Info
Sheets("Interests By Owners").Select
Sheets("Interests By Owners").Cells(x + 4, 9).Value = Sheets("Data Input").Range("P7").Value
Sheets("Interests By Owners").Cells(x + 5, 9).Value = Sheets("Data Input").Range("U7").Value
Sheets("Interests By Owners").Cells(x + 6, 9).Value = Sheets("Data Input").Range("P9").Value
Sheets("Interests By Owners").Cells(x + 7, 9).Value = Sheets("Data Input").Range("U9").Value
Sheets("Interests By Owners").Cells(x + 8, 9).Value = Sheets("Data Input").Range("W9").Value
Sheets("Interests By Owners").Cells(x + 9, 9).Value = Sheets("Data Input").Range("X9").Value
Sheets("Interests By Owners").Cells(x + 8, 11).Value = Sheets("Data Input").Range("P11").Value
Sheets("Interests By Owners").Cells(x + 9, 11).Value = Sheets("Data Input").Range("U11").Value
Sheets("Interests By Owners").Cells(x + 10, 9).Value = Sheets("Data Input").Range("P13").Value
Sheets("Interests By Owners").Cells(x + 10, 11).Value = Sheets("Data Input").Range("U13").Value

Application.ScreenUpdating = True

'get roster data
Sheets("Data Input").Select
y = Sheets("Data Input").Range("AC1999").End(xlUp).Row
b = y - 8

Sheets("Interests By Owners").Select
For n = 1 To b
Sheets("Interests By Owners").Cells(x + 14, 2).Select
Selection.EntireRow.Insert shift:=xlDown
Next n

Sheets("Data Input").Select
Range(Cells(9, 29), Cells(y, 45)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Interests By Owners").Select
Sheets("Interests By Owners").Cells(x + 13, 4).Select
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats





TheEnd:



End Sub
 

jgolvach

New Member
Joined
Sep 23, 2010
Messages
38
haha wow. thank you! I fixed that spelling glitch. unfortunately that didnt fix the problem.

The code I posted is no longer comparing concatenated strings. instead it is comparing the initial cells which the first and last names go into individually (thus fstnm & lstnm vs chkfstnm & chklstnm). Everything seems to work unless the following situation is true:

the new entry is for example "Anderson, Alex" and the first person on the list where the new entry is to be inserted is currently "Bonds, Barry". Basically the alphabetizing works for everything except the first entry in the interests by owners sheet is ignored.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

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
Top