Help with a VBA code to covert many addresses in multiple columns and rows to one column

[PFCSC]

Hello esteemed Excel users!

I am hoping you guys could help me save a lot of time in managing a database.

In one tab of a spreadsheet I have a simple contact list that specifies details about each company I have in the list. I have nearly 4000 companies in my list. So A1 is a header for Company Name, B1 is Contact Individual, C1 is Position/Title, D1 is Phone Number, etc. My spreadsheet has generic contact info headers all the way to cell U1. After all these specifying columns of contact details, I list out any addresses associated with the company to the right. Some companies have no addresses associated with it and some have 250 addresses associated with it. So starting in V1 all the way to JK1 there are headers for address1, address2, address3... address250 in JK1. Each address takes up only 1 cell.

I would like to be able to easily upload all of these addresses to a map and see where the presence of each company is. Or be able to look up multiple companies addresses at once.

I think the first step to getting there is I need a macro that can copy the entire tab range V2:JK3953 and transpose all the addresses into a new tab in a single vertical list starting in B3. If someone could get me that far that would be satisfactory and I'd be very grateful! But just in case if anyone is feeling ambitious, having the macro also paste the contact Company Name (can be found in the first tab column A) associated with each address pasted into column C next to the other addresses that would be even more amazing!

Please note that since some companies have no addresses associated with it and some have 250, a simple transpose of the data will create many blanks. Ideally, I hope we could get a list all in one column with no blanks in it. Just one address after the other all the way to the end of the list.

Please let me know if this is possible!

Thanks!

 

[Norie]

What would be in column A on the new tab?

 

[PFCSC]

As a general rule, I try to not use Column A in my working tabs. I prefer it empty just to keep it there for notes if I have any when I finish.

 

[PFCSC]

Sorry to bump this, just hoping someone could help before it is forgotten!

 

[PFCSC]

Hi everyone, just in case anyone in the future looks at this, I found a partial solution. I can now list out and transpose all of my addresses from multiple columns to a new column in order with no spaces. However, I am still working on a way to do a kind of reverse Vlookup. One that can tell me which Company Name belongs to each address. To recap, In column A I have (Company Name), B (Contact Individual) C (Position/Title) D (Phone Number)... all the way to V (address 1) to JK (address 250). the below code will take all of the addresses and put them in order to the right in column JP with no spaces.

Sub RangeToColumn()

Dim Address As Variant
Dim a As Long, b As Long, c As Long

Application.ScreenUpdating = False
c = 2

Address = Range("v2:jk4000").Value
For a = 1 To UBound(Address, 1)
For b = 1 To UBound(Address, 2)
Cells(c, 276).Value = Address(a, b)
c = c + 1
Next
Next

Application.ScreenUpdating = True

Range("JP2:JP15000").Select
Range("JP2").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("JP1").Select

End Sub


Now I just need a formula/macro to find the Company Name in column A associated with each sorted address and put the name next to each address in column JQ. So far the closest I have is the formula:
=INDEX(A:A,MATCH(JP2,V:V,0))
but this only works if the address is in column V. I cant figure out how to make it search for the value in the range of columns V:JK and return the Company Name value in column A.

Any help on this last part?