if cell is empty replace with value from another cell.

[MechEng_Yoshi]

I have a sheet that looks like this with these addresses it does through address1 through 6. i am trying to import this into a data base but this is the issue. some people have info for addresses in random spots so the import is not picking up after blanks. I want to figure out a way to check each column and if its blank to replace with value from other column. for example, if a person has no address1 or address2 but has address3 then move value from address 3 to address1.

 

[kweaver]

It would be helpful if you posted a sample using XL2BB rather than an image and an example or two of what you expect to happen and where.

 

[Peter_SSs]

Assumptions

1. Last row can be determined from column A
2. Last Address heading is the last column heading in row 1
3. First address heading is before column J
4. All address sections consist of 9 columns
5. Address data is not the result of formulas

If the above all hold true, try this with a copy of your data, otherwise provide more details and/or sample as requested above.
This moves whole sections of 9 columns left if a whole section is blank. Not sure if that is what you wanted or you want to look individual column by individual column.

Sub Compact_Addresses()
  Dim lc As Long, lr As Long, r As Long, c As Long, k As Long
 
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  For r = 2 To lr
    For c = lc - 8 To 1 Step -9
      k = 0
      On Error Resume Next
      k = Cells(r, c).Resize(, 9).SpecialCells(xlConstants).Count
      On Error GoTo 0
      If k = 0 Then Cells(r, c).Resize(, 9).Delete Shift:=xlToLeft
    Next c
  Next r
  Application.ScreenUpdating = True
End Sub

 

[MechEng_Yoshi]

Here is a sample

OnlyAddress.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1	Address1Type	Address1isPrimary	Address1Line1	Address1Line2	Address1Line3	Address1City	Address1County	Address1State	Address1Zip	Address2Type	Address2isPrimary	Address2Line1	Address2Line2	Address2Line3	Address2City	Address2County	Address2State	Address2Zip	Address3Type	Address3isPrimary	Address3Line1	Address3Line2	Address3Line3	Address3City	Address3County	Address3State	Address3Zip	Address4Type	Address4isPrimary	Address4Line1	Address4Line2	Address4Line3	Address4City	Address4County	Address4State	Address4Zip	Address5Type	Address5isPrimary	Address5Line1	Address5Line2	Address5Line3	Address5City	Address5County	Address5State	Address5Zip	Address6Type	Address6isPrimary	Address6Line1	Address6Line2	Address6Line3	Address6City	Address6County	Address6State	Address6Zip
2																																					Business	Yes	6977 San Pedro Ave			San Antonio	BEXAR	TX	78216-6245
3	Home	Yes	13715 street dr			San Antonio	BEXAR	TX	78216-1930
4										Home	Yes	511 stet dr			Horseshoe Bay	BEXAR	TX	78216-1930																												Last Known Business Address	Yes	1213 Iowa St			San Antonio	BEXAR	TX	78203-1852
5										Home	Yes	13 street dr			Horseshoe Bay	BEXAR	TX	78216-1930	Last Known Home Address	No	1803 Edison Dr			San Antonio	BEXAR	TX	78201-3514
6																			Last Known Home Address	No	3307 Tree Grove			San Antonio		TX	78247
7	Home	Yes	1903 street dr			San Antonio	BEXAR	TX	78227-5810
8																			Last Known Home Address	No	9928 E US Highway 79			Oakwood	GUADALUPE	TX	75855-3484
9																			Last Known Home Address	No	1747 Fawngate			San Antonio	BEXAR	TX	78248										Business	No	5335 Castroville Road			San Antonio		TX	78227
10	Home	Yes	206 street dr			San Antonio	BEXAR	TX	78213-4047																												Business	No	College of Business	One UTSA Circle		San Antonio		TX	78249
11										Home	Yes	12 Ter Rd			Horseshoe Bay	BEXAR	TX	78213-4047																			Business	Yes	401 S Frio St			San Antonio	BEXAR	TX	78207-4416
12	Home	Yes	232 street dr			San Antonio	BEXAR	TX	78212-2512
13	Home	Yes	511 street dr			San Antonio	BEXAR	TX	78212-1404										Last Known Home Address	No	7434 Branston			San Antonio	BEXAR	TX	78250										Business	No	9800 Fredericksburg Road			San Antonio	BEXAR	TX	78288-0001
14																																					Business	No	2304 First City Nat'l Bank Bld			Houston		TX	77002
15	Home	Yes	3 street dr			San Antonio	BEXAR	TX	78230-1526
16																																					Business	Yes	201 Central Park Mall			San Antonio	BEXAR	TX	78216
17	Home	Yes	102 Wifr Way			San Antonio	BEXAR	TX	78213-2640																												Business	No	9800 Fredericksburg Rd			San Antonio	BEXAR	TX	78288
18	Home	Yes	12 Viard Dr			San Antonio	BEXAR	TX	78257-1237										Last Known Home Address	No	3829 Bobstone Drive			Sherman Oaks	LOS ANGELES	CA	91423										Business	No	UTSA	MB 2.13	One UTSA Circle	San Antonio	BEXAR	TX	78249
19	Home	Yes	526 Pie Rd 7005			Edgewood	BEXAR	TX	75117-5221										Last Known Home Address	No	14 Devonwood			San Antonio	BEXAR	TX	78257										Business	No	3015 Blanco Road			San Antonio		TX	78212
20										Home	Yes	127 Peo Ave			San Antonio	BEXAR	TX	78216-1930	Last Known Home Address	No	11330 Marina Park			San Antonio	BEXAR	TX	78249
21																																					Business	Yes	210 N Campbell St			El Paso	EL PASO	TX	79901-1406
22	Home	Yes	13 W Em Grn			San Antonio	BEXAR	TX	78230-2631																												Business	No	Education & Culture Program	320 East 43rd Street		New York	NEW YORK	NY	10017	Last Known Business Address	No	601 Colorado St			Austin	TRAVIS	TX	78701-2904
23																																														Last Known Business Address	No	2829 Babcock Rd Ste 740			San Antonio	BEXAR	TX	78229-6015
24	Home	Yes	1312 Ter Rd			Colleyville	TARRANT	TX	76034-6201																												Business	No	PO Box 5240			Lubbock	LUBBOCK	TX	79408-5240
25																																					Business	Yes	1508 S Lone Star Way Ste 5			Edinburg	HIDALGO	TX	78539-3603
26										Home	Yes	130 Creeh Dr			San Antonio	BEXAR	TX	78216-1930	Last Known Home Address	No	348 Morningside Drive			San Antonio	BEXAR	TX	78209																			Last Known Business Address	No	100 W Houston St Ste 1452A			San Antonio	BEXAR	TX	78205-1490
27	Home	Yes	2915 Sad Dr			San Antonio	BEXAR	TX	78230-5035																												Business	No	7703 Floyd Curl Dr			San Antonio	BEXAR	TX	78229-3901
28																			Last Known Home Address	No	8401 N. New Braunfels			San Antonio		TX	78209
29	Home	Yes	16227 Peo Ave			San Antonio	BEXAR	TX	78232-3018										Last Known Home Address	No	13803 Bluff Wind			San Antonio		TX	78216
30	Home	No	PO Box 9186			Horseshoe Bay		TX	78657	Alternate Home Address	No	20 Paton Ave Apt 2			San Anben	BEJER	TX	78209-6264																			Business	Yes	112 E Pecan St, Ste 525			San Antonio	BEXAR	TX	78205	Last Known Business Address	No	112 E Pecan St Ste 525			San Antonio	BURNET	TX	78205
31																																					Business	Yes	PO Box 1865			San Antonio	BEXAR	TX	78297-1865
32	Home	Yes	834 Trar Rd			Blanco	BEXAR	TX	78606-2044																												Business	No	PO Box 790462			San Antonio	BEXAR	TX	78279
33	Home	Yes	3 Knood			San Antonio	BEXAR	TX	78248-2406
34	Home	Yes	1300 Cryk Dr			Austin	TRAVIS	TX	78746-4716										Last Known Home Address	No	202 Tumbleweed Trail S.			Austin	TRAVIS	TX	78733
35																			Last Known Home Address	No	30552 Blueberry Ridge			Bulverde	COMAL	TX	78163-2200
36	Home	Yes	1 Toes Pak Ln Apt 1015			San Antonio	BEXAR	TX	78209-6435										Last Known Home Address	No	1022 Navarro Street			San Antonio	BEXAR	TX	78205
37	Home	Yes	801 Laide Dr			Wimberley	BEXAR	TX	78676-4503																												Business	No	501 W Durango Blvd			San Antonio	BEXAR	TX	78207-4415	Last Known Business Address	No	145 Duncan Drive, Suite 200			San Antonio		TX	78226
Sheet1

 

[Peter_SSs]

Here is a sample

Can you now post the XL2BB expected results, done manually?
Say the first 8 rows should be enough.

Edit: That is, unless the previous macro does what you want.

 

[MechEng_Yoshi]

Yes I can post that. It works but doesn't do the job 100%. Thank you for your help. I would love to try and understand your thought process as well

 

[mohsinbipu]

As you excel version is Office365, then you can use TextJoin formula as below picture. Let me know, if it help to you?

=TEXTJOIN(", ", TRUE, A2,J2,S2,AB2,AK2,AT2)

 

[MechEng_Yoshi]

here are the first 8 rows manually conditioned.

OnlyAddress.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Address1Type	Address1isPrimary	Address1Line1	Address1City	Address1County	Address1State	Address1Zip	Address2Type	Address2isPrimary	Address2Line1	Address2City	Address2County	Address2State	Address2Zip
2	Business	Yes	6977 San Pedro Ave	San Antonio	BEXAR	TX	78216-6245	Business	Yes	6977 San Pedro Ave	San Antonio	BEXAR	TX	78216-6245
3	Home	Yes	13715 Bluffgate	San Antonio	BEXAR	TX	78216-1930
4	Last Known Business Address	Yes	1213 Iowa St	San Antonio
5	Last Known Home Address	No	1803 Edison Dr	San Antonio	BEXAR	TX	78201-3514
6	Last Known Home Address	No	3307 Tree Grove	San Antonio		TX	78247
7	Home	Yes	1903 White Magnolia	San Antonio	BEXAR	TX	78227-5810
8	Last Known Home Address	No	9928 E US Highway 79	Oakwood	GUADALUPE	TX	75855-3484
9	Last Known Home Address	No	1747 Fawngate	San Antonio	BEXAR	TX	78248	Business	No	5335 Castroville Road	San Antonio
Sheet1

 

[Peter_SSs]

here are the first 8 rows manually conditioned.

I have been away for a while but thanks for the manual results. They do raise further questions so if you still require help with this you will need to address these (at least).

1. From what I can see of row 2, there is only one address section in the original data (the '5' section). Why do your results repeat that address data twice?
   
   
2. In row 9 of the original data there are two sections with address data - the '3' section and the '5' section. Your results in row 9 contain one and a half sections of that data. Why does the results second section not contain the State and Zip columns?
   
   
3. Looks like I may have made a mistake in asking for 8 rows only. I notice in your results for the first 8 rows the 'Line2' and 'Line3' columns are missing. Some of the later rows (eg row 10) have data in those columns so does that data get omitted from the results or should your sample results in post #7 have included those (blank) columns?

Having asked those questions, it looks to me like my previous code does go pretty close to what you want and your said "It works but doesn't do the job 100%.". Perhaps you could explain and/or give examples of in what way it fails the 100% level?