CODEWITHSUNDEEP
excelvba
Jahir
Jahir

Reputation: 13

stacking same amount of duplicates + non duplicates of a cell

I am not sure how to express this in words, so perhaps a visual is better. Can somebody help me stack in the following way? States are in alphabetical order. So far I have been successful at stacking unique or duplicates, but i cant get to stack the way I really want. Perhaps my best description is to put the max number of states in both columns, but then im not sure how to pair them with the respective cities. 

Sheet1
State  City
FL     TPA
FL     FTL
MO     STL  
NV     RNO  
TX     HSTN 
TX     AUS


Sheet2
State  City
FL     TPA
FL     FTL
MO     STL      
MO     KSC
MO     HNB
NY     NY
TX     AMR
TX     NWT


Sheet3
State  City
FL     TPA
FL     FTL
MO     STL
MO     KSC
MO     HNB
NV     RNO
NY     NY
TX     HST
TX     AUS
TX     AMR
TX     NWT

Upvotes: 1

Views: 85

Answers (2)

Slai
Slai

Reputation: 22876

Sounds like you can just copy both sheets, Remove Duplicates, and Sort:

Sheet1.Cells.Copy Sheet3.Cells
Sheet2.UsedRange.Copy Sheet3.Cells.End(xlDown)(2)
Sheet3.Cells.RemoveDuplicates Array(1, 2)
Sheet3.Cells.Sort Sheet3.Columns(1), , Sheet3.Columns(2), , , , , xlYes

Upvotes: 1

A.S.H
A.S.H

Reputation: 29352

This one needs heavy use of Dictionary. Basically you need one for the states, and for each state its own dictionary of cities. Try this code, it produces the exact same output of Sheet3 in your example:

Sub MergeStatesAndCities()
  Dim r As Range, dicStates As Object, sh As Worksheet
  Set dicStates = CreateObject("Scripting.Dictionary")

  For Each sh In ThisWorkbook.Worksheets
    For Each r In sh.Range("A2", sh.Range("A999999").End(xlUp))
      'set a dictionary of cities for each state!
      If Not dicStates.Exists(r.Text) Then Set dicStates(r.Text) = CreateObject("Scripting.Dictionary")
      dicStates(r.Text)(r.Offset(, 1).Text) = 0 ' add key for the city if not already there
    Next
  Next

  ' Now bring the values into new sheet
  Set sh = Sheets.Add
  sh.name = "AllStatesAndCities"
  sh.Range("A1:B1").Value = Array("State", "City")
  Dim state
  For Each state In dicStates.keys
    With sh.Range("A999999").End(xlUp).Offset(1).Resize(dicStates(state).Count)
      .Value = state
      .Offset(, 1).Value = Application.Transpose(dicStates(state).keys)
    End With
  Next

  ' Now Sort the result by states
  sh.UsedRange.Columns("A:B").Sort key1:=sh.Range("A2"), Header:=xlYes

End Sub

Upvotes: 0

Related Questions