Importing Data from Excel - Transpose - Lots of Data

[Kaloyanides]

Good Morning!

I'm looking for help importing data into Access from Excel

I have:

ITEM# PRODUCTNAME DATE1 DATE2 DATE3 DATE4 DATE5 DATE6 DATE7 ETC...
001 NAME1 COST1 COST2 COST3 COST4 COST5 ETC...
002 NAME2 COST1 COST2 COST3 COST4 COST5 ETC...

I have about 1,000 items and about 60 columns with a date in the column heading and then the recorded cost for each item below.

I need to get this into Access so that every item and all dates/costs associated with each item listed so the data can be displayed in a continuous form attached to each item master record.

Hopefully this makes sense and there is someone that can help! Otherwise, I lose the data or spend countless hours copying and pasting.

Thanks!

[JoeM]

IMO, the easiest thing to do is to create an Excel macro that cleans up the data for you, and saves the file.
Then, import that cleaned up file into your Access table.

If you need help doing that, if you can provide a small actual detailed example of what the data currently looks like, and what it needs to look like to import into Access, I can help you with that Excel macro.
Be sure to indicate what columns that data is in in Excel, and what row the data starts on.

[Kaloyanides]

That would be great if you could help. Beyond my abilities. The item number goes across the top and the dates go down. This is a sample of items and dates. I also have it the other way with the items going down and the dates going across the top. Not sure if it makes a difference. I was playing trying to find a way to get into access. Thanks!

ITEMNO	1000	1001	1002	1003	1004	1005	1006
PRODUCT	1/2" Crinkle Cut French Fries	3/8" Clear Coat French Fries	3/8" Seasoned Fries	3/8" Straight Fries	3/8" Thin Cut French Fries	Golden Patties	Coated Fries NP
18-Apr
17-Apr
15-Apr
14-Apr
13-Apr
12-Apr		$25.11	$29.14		$26.95		$18.50
11-Apr						$37.68
10-Apr
8-Apr
7-Apr
4/6
4/5		$25.11	$29.14	$16.15			$18.50
4/4		$25.11	$29.14	$16.15			$18.50
4/3		$25.11	$29.14	$16.15			$18.50
3/24		$25.11	$29.14	$16.15			$18.50
2/8		$25.11	$29.14	$16.15			$18.50
2/7		$25.11	$29.14	$16.15			$18.50

[JoeM]

Here is Excel VBA code to clean up your data.
It takes the data from Sheet1, and pastes it to Sheet2 in the cleaned up format. You may need to change the sheet name references.

Sub MyCopy()

    Dim srcWS As Worksheet
    Dim destWS As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    Dim destRow As Long
    
    Application.ScreenUpdating = False
    
'   Set source and destination worksheets
    Set srcWS = Sheets("Sheet1")
    Set destWS = Sheets("Sheet2")
    
'   Pre-populate headers on destination sheet
    destWS.Range("A1") = "ITEMNO"
    destWS.Range("B1") = "PRODUCT"
    destWS.Range("C1") = "DATE"
    destWS.Range("D1") = "PRICE"
    destRow = 1
    
'   Find last row/column with data on source sheet
    lastRow = srcWS.Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = srcWS.Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns starting in column 2
    For myCol = 2 To lastCol
'       Loop through all rows starting in row 3
        For myRow = 3 To lastRow
'           Check to see if there is a price, if so copy to destination sheet
            If srcWS.Cells(myRow, myCol) > 0 Then
                destRow = destRow + 1
                destWS.Cells(destRow, "A") = srcWS.Cells(1, myCol)
                destWS.Cells(destRow, "B") = srcWS.Cells(2, myCol)
                destWS.Cells(destRow, "C") = srcWS.Cells(myRow, "A")
                destWS.Cells(destRow, "D") = srcWS.Cells(myRow, myCol)
            End If
        Next myRow
    Next myCol

'   Format date column on destination sheet
    destWS.Columns("C:C").NumberFormat = "m/d/yyyy"
    
    Application.ScreenUpdating = True
    
End Sub

[Kaloyanides]

Exactly what I needed!!! Thanks a million!!!

Here is Excel VBA code to clean up your data.
It takes the data from Sheet1, and pastes it to Sheet2 in the cleaned up format. You may need to change the sheet name references.

Sub MyCopy()

    Dim srcWS As Worksheet
    Dim destWS As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    Dim destRow As Long
    
    Application.ScreenUpdating = False
    
'   Set source and destination worksheets
    Set srcWS = Sheets("Sheet1")
    Set destWS = Sheets("Sheet2")
    
'   Pre-populate headers on destination sheet
    destWS.Range("A1") = "ITEMNO"
    destWS.Range("B1") = "PRODUCT"
    destWS.Range("C1") = "DATE"
    destWS.Range("D1") = "PRICE"
    destRow = 1
    
'   Find last row/column with data on source sheet
    lastRow = srcWS.Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = srcWS.Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns starting in column 2
    For myCol = 2 To lastCol
'       Loop through all rows starting in row 3
        For myRow = 3 To lastRow
'           Check to see if there is a price, if so copy to destination sheet
            If srcWS.Cells(myRow, myCol) > 0 Then
                destRow = destRow + 1
                destWS.Cells(destRow, "A") = srcWS.Cells(1, myCol)
                destWS.Cells(destRow, "B") = srcWS.Cells(2, myCol)
                destWS.Cells(destRow, "C") = srcWS.Cells(myRow, "A")
                destWS.Cells(destRow, "D") = srcWS.Cells(myRow, myCol)
            End If
        Next myRow
    Next myCol

'   Format date column on destination sheet
    destWS.Columns("C:C").NumberFormat = "m/d/yyyy"
    
    Application.ScreenUpdating = True
    
End Sub

[JoeM]

You are welcome!