Have a sheet with approx. 900+ rows. I need a formula that will automatically transpose items horizontally if the address is the same. (see pic)

[bgiovanni1]

I am trying to save lots of time and headache, so any help is appreciated! There are many addresses that have multiple tenants. I need the tenants horizontally transposed into the same row so that I can use this as a mail merge. I guess part two of this is that it would be great to also have a way to quickly and easily delete the other duplicates after this is done without having to do it manually, but that is not as important. Thank you in advance for your help!

Brenda

 

[sandy666]

Ops, I can't help, sorry

 

[bgiovanni1]

No worries, thanks for your solution otherwise!

 

[DanteAmor]

Welcome to the Board!

Try this macro.
Results in cell M2 down

Sub transpose_items()
  Dim a() As Variant, dic As Object, ky As Variant
  Dim v As Long, i As Long, j As Long
 
  a = Range("A2:J" & Range("A" & Rows.Count).End(xlUp).Row).Value
  Set dic = CreateObject("Scripting.Dictionary")
 
  For i = 1 To UBound(a, 1)
    dic(a(i, 10)) = dic(a(i, 10)) & a(i, 1) & "|"
  Next
  j = 2
  For Each ky In dic.keys
    v = Len(dic(ky)) - Len(Replace(dic(ky), "|", ""))
    Range("M" & j).Resize(1, v).Value = Split(dic(ky), "|")
    Range("V" & j).Value = ky
    j = j + 1
  Next
End Sub

 

[sandy666]

No worries, thanks for your solution otherwise!

You are welcome
maybe try with Example File
Have a nice day