can anyone assist with an excel access normalisation problem

990405 · Jun 29, 2004

Could you kindly assist with following.
I presently have records stored in Excel and would like to know how I could transfer these records into Access and also normalise into tables and also remove duplicates.

given below typical Excel records

Company name, tel, fax, SIC sector
A company may operate in several sectors, i.e clothing

ACME, tel, fax, clothing womens
ACME, tel, fax, clothing mens
ACME, tel, fax, clothing childrens
ACME, tel, fax, clothing sporting
ACME, tel, fax, clothing hosiery

I would like to get to

ACME, tel, fax, clothing womens
clothing mens
clothing childrens
clothing sporting
clothing hosiery

the company name (same tel & fax) in a record at times is duplicated, but what differs is the sector.
I would like to retain the company name (once), but not remove the differing sectors it operates in.

I have mangaged to remove duplicates, but the solution is partial and inadequate, since it keeps the first record it meets, but removes subsequent sectors.
i.e.
ACME, tel, fax, clothing womens

any assistance?

thanking you in advance

Smitty · Jun 29, 2004

Re: can anyone assist with an excel access normalisation pro

Welcome to the Board!

You can concatenate to create a unique field. You can then peform a de-dupe query in Access to get what you need. Or we can post some code to delete duplicates.

Book3

A

B

C

D

E

1

ACME

tel

fax

clothingwomens

ACMEclothingwomens

2

ACME

tel

fax

clothingmens

ACMEclothingmens

3

ACME

tel

fax

clothingchildrens

ACMEclothingchildrens

4

ACME

tel

fax

clothingsporting

ACMEclothingsporting

5

ACME

tel

fax

clothinghosiery

ACMEclothinghosiery

Sheet1

Hope that helps,

Smitty

Aladin Akyurek · Jun 29, 2004

Re: can anyone assist with an excel access normalisation pro

Is clothing the only qualifier regarding the SIC Sectors?

990405 · Jul 1, 2004

Re: can anyone assist with an excel access normalisation pro

could you send some code to remove duplicates and what is procedure for de-dupes in access?

thanks fo your assistance

990405 · Jul 1, 2004

Re: can anyone assist with an excel access normalisation pro

Aladin Akyurek said:
Is clothing the only qualifier regarding the SIC Sectors?

sic is a field, the sic code can be many different sectors from food to industrial compnents or services

Smitty · Jul 1, 2004

Re: can anyone assist with an excel access normalisation pro

Here's the delete code for Excel:

Sub Column_Sort_DeleteDuplicates_()
 ' Code by Ponsy Nob.
 Dim col%, c%, x%, rng As Range
 col = 1 '1 is the column number to search - change it as required
 ' I.E. Column A = 1, Column B = 2, etc

 ' Set the column number of the column to store temporary formula
 c = Range([A1], ActiveSheet.UsedRange).Columns.Count + 1
 ' Exit if the used columns=256 (instead of exiting, could amend the code _
 to look for a column containing no data to use for the temporary formula)
 If c > 256 Then Exit Sub
 ' Set the column offset(from the search column to the temp. formula column)
 x = col - c

 ' Turn off screen-updating and set calculation to manual _
 (if required, could add a test to check if calculation is already set _
 to manual, in which case would not want to set to automatic at the end
 With Application
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 ' Set the range to contain the temp. formula
 Set rng = Range(Cells(1, c), Cells(65536, col).End(xlUp).Offset(, -x))
 With rng
 ' Sort the data by the search column
 .EntireRow.Sort Key1:=rng(1, x + 1), Order1:=xlAscending, Header:=xlNo
 ' Put temp. value in the fors cell of the temp.column
 rng(1) = 1
 ' Enter the temp formula that identifies duplicates
 .Offset(1).FormulaR1C1 = "=IF(RC[" & x & "]=R[-1]C[" & x & "],"""",1)"
 ' Convert the formula to value
 .Offset(1) = .Offset(1).Value
 ' Sort entire rows by the temp. formula result so as to group all of _
 the rows to be deleted at the end
 .EntireRow.Sort Key1:=rng(1), Order1:=xlAscending, Header:=xlNo
 ' Error handler in case there are no duplicates
 On Error Resume Next
 ' Delete the duplicate rows
 .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 ' Reset error handling
 On Error GoTo 0
 End With
 ' Delete the temp. formula column
 Columns(c).Delete
 ' Reset the sheet's last used cell
 ActiveSheet.UsedRange
 ' Turn on screen-updating and set calculation to automatic
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 End With

End Sub


For Access, check the helpfile. It documents the procedure very well.

Smitty

Aladin Akyurek · Jul 1, 2004

Re: can anyone assist with an excel access normalisation pro

990405 said:
Aladin Akyurek said:

Is clothing the only qualifier regarding the SIC Sectors?

Click to expand...

sic is a field, the sic code can be many different sectors from food to industrial compnents or services

I'm not sure this answers my question... It seems to me that you should first normalize & the following tables might be sufficient to cover the sample data you provided:

COMPANIES(Company#,Cname,Ctel,Cfax)

SIC-SECTORS(Sector#,Description)

COMPANY-SECTOR DETAILS(Company#,Sector#)

A record from COMPANIES would be:

ACME,ACME International,978-9876971,978-98766972

A record from SIC-SECTORS...

S0001,Clothing Mens

A record from the last table...

ACME,S0001

If you create 3 worksheets carrying table names as sheet names, you can import these sheets easily into Access, then fix the data types for each field, and set the relationships between them.

990405 · Jul 13, 2004

Re: can anyone assist with an excel access normalisation pro

pennysaver said:
Welcome to the Board!

You can concatenate to create a unique field. You can then peform a de-dupe query in Access to get what you need. Or we can post some code to delete duplicates.
Book3
A B C D E
1 ACME tel fax clothingwomens ACMEclothingwomens
2 ACME tel fax clothingmens ACMEclothingmens
3 ACME tel fax clothingchildrens ACMEclothingchildrens
4 ACME tel fax clothingsporting ACMEclothingsporting
5 ACME tel fax clothinghosiery ACMEclothinghosiery
Sheet1

Hope that helps,

Smitty

********************
Procedure?
could you send some code to remove duplicates for Access (query) and what is procedure for "de-dupes" in access?

Is this a normalisation problem and as such can this be resolved via better normalisation?

thanks
thanks fo your assistance

Smitty · Jul 13, 2004

Re: can anyone assist with an excel access normalisation pro

Here is some code for deleting duplicates in Excel:

Sub Column_Sort_DeleteDuplicates_()
 ' Code by Ponsy Nob.
 Dim col%, c%, x%, rng As Range
 col = 1 '1 is the column number to search - change it as required
 ' I.E. Column A = 1, Column B = 2, etc

 ' Set the column number of the column to store temporary formula
 c = Range([A1], ActiveSheet.UsedRange).Columns.Count + 1
 ' Exit if the used columns=256 (instead of exiting, could amend the code _
 to look for a column containing no data to use for the temporary formula)
 If c > 256 Then Exit Sub
 ' Set the column offset(from the search column to the temp. formula column)
 x = col - c

 ' Turn off screen-updating and set calculation to manual _
 (if required, could add a test to check if calculation is already set _
 to manual, in which case would not want to set to automatic at the end
 With Application
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 ' Set the range to contain the temp. formula
 Set rng = Range(Cells(1, c), Cells(65536, col).End(xlUp).Offset(, -x))
 With rng
 ' Sort the data by the search column
 .EntireRow.Sort Key1:=rng(1, x + 1), Order1:=xlAscending, Header:=xlNo
 ' Put temp. value in the fors cell of the temp.column
 rng(1) = 1
 ' Enter the temp formula that identifies duplicates
 .Offset(1).FormulaR1C1 = "=IF(RC[" & x & "]=R[-1]C[" & x & "],"""",1)"
 ' Convert the formula to value
 .Offset(1) = .Offset(1).Value
 ' Sort entire rows by the temp. formula result so as to group all of _
 the rows to be deleted at the end
 .EntireRow.Sort Key1:=rng(1), Order1:=xlAscending, Header:=xlNo
 ' Error handler in case there are no duplicates
 On Error Resume Next
 ' Delete the duplicate rows
 .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 ' Reset error handling
 On Error GoTo 0
 End With
 ' Delete the temp. formula column
 Columns(c).Delete
 ' Reset the sheet's last used cell
 ActiveSheet.UsedRange
 ' Turn on screen-updating and set calculation to automatic
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 End With

End Sub


As for how to do it in Access, look in the helpfile for delete duplicates. It's very well documented.

Smitty

can anyone assist with an excel access normalisation problem

990405

Board Regular

Excel Facts

Smitty

Legend

Aladin Akyurek

MrExcel MVP

990405

Board Regular

990405

Board Regular

Smitty

Legend

Aladin Akyurek

MrExcel MVP

990405

Board Regular

Smitty

Legend

Similar threads

Forum statistics

Share this page

can anyone assist with an excel access normalisation problem

Board Regular

Excel Facts

Legend

MrExcel MVP

Board Regular

Board Regular

Legend

MrExcel MVP

Board Regular

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock