Home

Excel add multiple line using sort filter

%3CLINGO-SUB%20id%3D%22lingo-sub-302854%22%20slang%3D%22en-US%22%3EExcel%20add%20multiple%20line%20using%20sort%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302854%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20one%20big%20file%20contain%20couple%20columns%20like%20Country%2C%20State%2C%20City%2C%20Zip%20Code.%20I%20want%20to%20add%20a%20line%20after%20each%20city.%20I%20have%20tried%20at%20my%20level%20best%20but%20couldn't%20find%20the%20solution.%20I%20have%20already%20sorted%20by%20Country%2C%20State%2C%20City%20and%20Zip%20code.%20Now%20my%20city%20name%20list%20is%20A%20to%20Z%20for%20each%20state%20and%20country.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-302854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303087%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20add%20multiple%20line%20using%20sort%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303087%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%20You%20just%20want%20to%20insert%20a%20row%20in%20between%20each%20city%20then%3F%20Ie.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20is%20correct%2C%20here%20is%20a%20VBA%20option%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20AddRows()%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20FirstDataRow%20As%20Integer%3CBR%20%2F%3EDim%20LastDataRow%20As%20Integer%3CBR%20%2F%3EDim%20CityColumn%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EFirstDataRow%20%3D%202%3CBR%20%2F%3ELastDataRow%20%3D%2013%3CBR%20%2F%3ECityColumn%20%3D%209%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3EDim%20Cell%20As%20Range%3CBR%20%2F%3EDim%20CityName%20As%20String%3CBR%20%2F%3EDim%20CityName2%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%20LastDataRow%20To%20FirstDataRow%20Step%20-1%3CBR%20%2F%3E%3CBR%20%2F%3ECityName%20%3D%20Cells(i%2C%20CityColumn).Value%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20CityName%20%3D%20CityName2%20Then%3CBR%20%2F%3E%20Rows(i%20%2B%201%20%26amp%3B%20%22%3A%22%20%26amp%3B%20i%20%2B%201).Insert%20Shift%3A%3DxlDown%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ECityName2%20%3D%20CityName%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3EJust%20need%20to%20change%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFirstDataRow%20%3D%202%3CBR%20%2F%3ELastDataRow%20%3D%2013%3CBR%20%2F%3ECityColumn%20%3D%209%3C%2FPRE%3E%3CP%3ETo%20the%20appropriate%20row%2Fcolumn%20numbers%20for%20your%20worksheet%20and%20run%20the%20macro.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Kartikey5
Occasional Visitor

I have one big file contain couple columns like Country, State, City, Zip Code. I want to add a line after each city. I have tried at my level best but couldn't find the solution. I have already sorted by Country, State, City and Zip code. Now my city name list is A to Z for each state and country.

1 Reply
Highlighted

HI, You just want to insert a row in between each city then? Ie.

 

City1

City1

City2

City2

City3

City3

 

to 

 

City1

City1

 

City2

City2

 

City3

City3

 

Assuming that is correct, here is a VBA option:

 

Sub AddRows()

Dim FirstDataRow As Integer
Dim LastDataRow As Integer
Dim CityColumn As Integer

FirstDataRow = 2
LastDataRow = 13
CityColumn = 9

Dim i As Integer
Dim Cell As Range
Dim CityName As String
Dim CityName2 As String


For i = LastDataRow To FirstDataRow Step -1

CityName = Cells(i, CityColumn).Value

If Not CityName = CityName2 Then
Rows(i + 1 & ":" & i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If

CityName2 = CityName

Next i

End Sub

Just need to change 

 

FirstDataRow = 2
LastDataRow = 13
CityColumn = 9

To the appropriate row/column numbers for your worksheet and run the macro. 

Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies