fill data gaps (1 Viewer)

[mikeo1313]

I am going to remove duplicate company names but not all records have phone, fax and email.

For example.

company.... phone.... fax .....email

abc ......... 123
abc ......... ......... 456
abc ......... ......... ......... abc@abc.com
abc ......... 123

def ......... 456
def
def
def ......... ......... ......... def@def.com

ghi
ghi ......... ......... 123
ghi


If I delete duplicates, I dont want to loose data that might be available from another record for the same company.

From what you can see not all records have phone #, fax & email,,, if they have them atall.

After running the vba, I'd like the data above to look like this

company.... phone.... fax ....email

abc ......... 123 ......... 456 ......... abc@abc.com
abc ......... 123 ......... 456 ......... abc@abc.com
abc ......... 123 ......... 456 ......... abc@abc.com
abc ......... 123 ......... 456 ......... abc@abc.com

def ......... 456 ......... ......... def@def.com
def ......... 456 ......... ......... def@def.com
def ......... 456 ......... ......... def@def.com
def ......... 456 ......... ......... def@def.com

ghi ......... ......... 123
ghi ......... ......... 123
ghi ......... ......... 123


So I can delete the duplicates without trouble.

jik, the periods are there since the spaces were removed upon posting.

 

[Galaxiom]

Use a query. Drag the table into the designer twice. Join it to itself on the company field.

First run the query with the criteria to detect conflicting information.

WHERE Table.fieldname <> Table_1.fieldname

Duplicate these conditions and OR them for each field.

Once you are have sorted these conflicts change to an update query with the criteria to update the Null values.