if cell is empty replace with value from another cell.

MechEng_Yoshi

New Member
Joined
Oct 2, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • example.PNG
    example.PNG
    72 KB · Views: 42

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Here is a sample

OnlyAddress.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB
1Address1TypeAddress1isPrimaryAddress1Line1Address1Line2Address1Line3Address1CityAddress1CountyAddress1StateAddress1ZipAddress2TypeAddress2isPrimaryAddress2Line1Address2Line2Address2Line3Address2CityAddress2CountyAddress2StateAddress2ZipAddress3TypeAddress3isPrimaryAddress3Line1Address3Line2Address3Line3Address3CityAddress3CountyAddress3StateAddress3ZipAddress4TypeAddress4isPrimaryAddress4Line1Address4Line2Address4Line3Address4CityAddress4CountyAddress4StateAddress4ZipAddress5TypeAddress5isPrimaryAddress5Line1Address5Line2Address5Line3Address5CityAddress5CountyAddress5StateAddress5ZipAddress6TypeAddress6isPrimaryAddress6Line1Address6Line2Address6Line3Address6CityAddress6CountyAddress6StateAddress6Zip
2BusinessYes6977 San Pedro AveSan AntonioBEXARTX78216-6245
3HomeYes13715 street drSan AntonioBEXARTX78216-1930
4HomeYes511 stet drHorseshoe BayBEXARTX78216-1930Last Known Business AddressYes1213 Iowa StSan AntonioBEXARTX78203-1852
5HomeYes13 street drHorseshoe BayBEXARTX78216-1930Last Known Home AddressNo1803 Edison DrSan AntonioBEXARTX78201-3514
6Last Known Home AddressNo3307 Tree GroveSan AntonioTX78247
7HomeYes1903 street drSan AntonioBEXARTX78227-5810
8Last Known Home AddressNo9928 E US Highway 79OakwoodGUADALUPETX75855-3484
9Last Known Home AddressNo1747 FawngateSan AntonioBEXARTX78248BusinessNo5335 Castroville RoadSan AntonioTX78227
10HomeYes206 street drSan AntonioBEXARTX78213-4047BusinessNoCollege of BusinessOne UTSA CircleSan AntonioTX78249
11HomeYes12 Ter RdHorseshoe BayBEXARTX78213-4047BusinessYes401 S Frio StSan AntonioBEXARTX78207-4416
12HomeYes232 street drSan AntonioBEXARTX78212-2512
13HomeYes511 street drSan AntonioBEXARTX78212-1404Last Known Home AddressNo7434 BranstonSan AntonioBEXARTX78250BusinessNo9800 Fredericksburg RoadSan AntonioBEXARTX78288-0001
14BusinessNo2304 First City Nat'l Bank BldHoustonTX77002
15HomeYes3 street drSan AntonioBEXARTX78230-1526
16BusinessYes201 Central Park MallSan AntonioBEXARTX78216
17HomeYes102 Wifr WaySan AntonioBEXARTX78213-2640BusinessNo9800 Fredericksburg RdSan AntonioBEXARTX78288
18HomeYes12 Viard DrSan AntonioBEXARTX78257-1237Last Known Home AddressNo3829 Bobstone DriveSherman OaksLOS ANGELESCA91423BusinessNoUTSAMB 2.13One UTSA CircleSan AntonioBEXARTX78249
19HomeYes526 Pie Rd 7005EdgewoodBEXARTX75117-5221Last Known Home AddressNo14 DevonwoodSan AntonioBEXARTX78257BusinessNo3015 Blanco RoadSan AntonioTX78212
20HomeYes127 Peo AveSan AntonioBEXARTX78216-1930Last Known Home AddressNo11330 Marina ParkSan AntonioBEXARTX78249
21BusinessYes210 N Campbell StEl PasoEL PASOTX79901-1406
22HomeYes13 W Em GrnSan AntonioBEXARTX78230-2631BusinessNoEducation & Culture Program320 East 43rd StreetNew YorkNEW YORKNY10017Last Known Business AddressNo601 Colorado StAustinTRAVISTX78701-2904
23Last Known Business AddressNo2829 Babcock Rd Ste 740San AntonioBEXARTX78229-6015
24HomeYes1312 Ter RdColleyvilleTARRANTTX76034-6201BusinessNoPO Box 5240LubbockLUBBOCKTX79408-5240
25BusinessYes1508 S Lone Star Way Ste 5EdinburgHIDALGOTX78539-3603
26HomeYes130 Creeh DrSan AntonioBEXARTX78216-1930Last Known Home AddressNo348 Morningside DriveSan AntonioBEXARTX78209Last Known Business AddressNo100 W Houston St Ste 1452ASan AntonioBEXARTX78205-1490
27HomeYes2915 Sad DrSan AntonioBEXARTX78230-5035BusinessNo7703 Floyd Curl DrSan AntonioBEXARTX78229-3901
28Last Known Home AddressNo8401 N. New BraunfelsSan AntonioTX78209
29HomeYes16227 Peo AveSan AntonioBEXARTX78232-3018Last Known Home AddressNo13803 Bluff WindSan AntonioTX78216
30HomeNoPO Box 9186Horseshoe BayTX78657Alternate Home AddressNo20 Paton Ave Apt 2San AnbenBEJERTX78209-6264BusinessYes112 E Pecan St, Ste 525San AntonioBEXARTX78205Last Known Business AddressNo112 E Pecan St Ste 525San AntonioBURNETTX78205
31BusinessYesPO Box 1865San AntonioBEXARTX78297-1865
32HomeYes834 Trar RdBlancoBEXARTX78606-2044BusinessNoPO Box 790462San AntonioBEXARTX78279
33HomeYes3 KnoodSan AntonioBEXARTX78248-2406
34HomeYes1300 Cryk DrAustinTRAVISTX78746-4716Last Known Home AddressNo202 Tumbleweed Trail S.AustinTRAVISTX78733
35Last Known Home AddressNo30552 Blueberry RidgeBulverdeCOMALTX78163-2200
36HomeYes1 Toes Pak Ln Apt 1015San AntonioBEXARTX78209-6435Last Known Home AddressNo1022 Navarro StreetSan AntonioBEXARTX78205
37HomeYes801 Laide DrWimberleyBEXARTX78676-4503BusinessNo501 W Durango BlvdSan AntonioBEXARTX78207-4415Last Known Business AddressNo145 Duncan Drive, Suite 200San AntonioTX78226
Sheet1
 
Upvote 0
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
 
Upvote 0
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)
 

Attachments

  • textjoin.JPG
    textjoin.JPG
    157.4 KB · Views: 14
Upvote 0
here are the first 8 rows manually conditioned.

OnlyAddress.xlsx
ABCDEFGHIJKLMN
1Address1TypeAddress1isPrimaryAddress1Line1Address1CityAddress1CountyAddress1StateAddress1ZipAddress2TypeAddress2isPrimaryAddress2Line1Address2CityAddress2CountyAddress2StateAddress2Zip
2BusinessYes6977 San Pedro AveSan AntonioBEXARTX78216-6245BusinessYes6977 San Pedro AveSan AntonioBEXARTX78216-6245
3HomeYes13715 BluffgateSan AntonioBEXARTX78216-1930
4Last Known Business AddressYes1213 Iowa StSan Antonio
5Last Known Home AddressNo1803 Edison DrSan AntonioBEXARTX78201-3514
6Last Known Home AddressNo3307 Tree GroveSan AntonioTX78247
7HomeYes1903 White MagnoliaSan AntonioBEXARTX78227-5810
8Last Known Home AddressNo9928 E US Highway 79OakwoodGUADALUPETX75855-3484
9Last Known Home AddressNo1747 FawngateSan AntonioBEXARTX78248BusinessNo5335 Castroville RoadSan Antonio
Sheet1
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top