Auto populate Data For Empty Fields

[Pablo]

Hi,

My data table has a serious flaw, and unfortunately it is an import table.
I'm trying to fix this table but I need serious help. Here's the situation:

Change ID Change Category
1 a
b
c
2 a
b
c
3 a

As you can see, some rows don't carry the corresponding "Change ID." Is
there a way to automotically drag the change id until it reaches the next
Change ID number. For example, drag down the change ID 1 until it recognizes
change ID #2, then drag change ID 2 until it finds change ID # 3.

Please let me know if this is possible.

Thanks,

 

[Jeff Boyce]

Pablo

You posted in a newsgroup dedicated to MS Access, the relational database
product.

What you describe sounds like how you'd do something in Excel, the
spreadsheet. Have you considered doing this in Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pablo]

I might have to do it in Excel b/c this data is pretty screwy... I wanted to
do it in Access b/c it's easier to analyze.

Anyway, should I repost in the Excel forum? Same question in Excel terms...

Thanks,

 

[John W. Vinson]

Hi,

My data table has a serious flaw, and unfortunately it is an import table.
I'm trying to fix this table but I need serious help. Here's the situation:

Change ID Change Category
1 a
b
c
2 a
b
c
3 a

As you can see, some rows don't carry the corresponding "Change ID." Is
there a way to automotically drag the change id until it reaches the next
Change ID number. For example, drag down the change ID 1 until it recognizes
change ID #2, then drag change ID 2 until it finds change ID # 3.

Please let me know if this is possible.

Thanks,

Not very easily. You may need to Link to the import source, and write code to
step through the records one at a time, adding the ChangeID if it's blank.
Once you've imported it into a table there is nothing to distinguish the
second row in your example (blank, b) from the fifth row (also blank, b).
Tables have no "record number" or defined order!

 

[Jeff Boyce]

Yes, posting to the Excel 'groups will get folks looking at your question
with more Excel experience.

And after you get the updates done, you could always re-import to Access
from Excel.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maarkr]

well, this may help if you play around with it...I call this from a module
that copies the cells down in the A row.. then it appends the spreadsheet
data to a table...

Private Sub CopyCellsDown()
'shown as an example to run excel macro actions from Access
'fills and copies blank rows in col A

Dim appXL As Excel.Application
Set appXL = New Excel.Application
appXL.Visible = False

appXL.Workbooks.Open "S:\Auth_Assign2.xls"

Range("A2:A200").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Sheets("Assigned Totals").Select
Range("A2:A200").Select
Selection.FormulaR1C1 = "=+R[-1]C"


appXL.ActiveWorkbook.Save
appXL.Quit

End Sub

 

[Pablo]

Hi Maarkr,

I was able to insert the module and run it. However, it only copies the
value of the first change ID. In other words, it doesn't recognize to stop
when it encounters a non-blank in order to begin the process again. Do you
know of a further way to run this module?

Thx,

PC

well, this may help if you play around with it...I call this from a module
that copies the cells down in the A row.. then it appends the spreadsheet
data to a table...

Private Sub CopyCellsDown()
'shown as an example to run excel macro actions from Access
'fills and copies blank rows in col A

Dim appXL As Excel.Application
Set appXL = New Excel.Application
appXL.Visible = False

appXL.Workbooks.Open "S:\Auth_Assign2.xls"

Range("A2:A200").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Sheets("Assigned Totals").Select
Range("A2:A200").Select
Selection.FormulaR1C1 = "=+R[-1]C"


appXL.ActiveWorkbook.Save
appXL.Quit

End Sub

Hi,

My data table has a serious flaw, and unfortunately it is an import table.
I'm trying to fix this table but I need serious help. Here's the situation:

Change ID Change Category
1 a
b
c
2 a
b
c
3 a

As you can see, some rows don't carry the corresponding "Change ID." Is
there a way to automotically drag the change id until it reaches the next
Change ID number. For example, drag down the change ID 1 until it recognizes
change ID #2, then drag change ID 2 until it finds change ID # 3.

Please let me know if this is possible.

Thanks,

 

[Maarkr]

without seeing exactly how your data is formatted, I can't reply, but if you
use the Excel Disc. Group to solve your problem in Excel, what u do is record
a macro in Excel to recreate the solution and format it appropriately, then
copy and paste the Excel macro in the Sub I used below between the
'appxl.workbooks.open...' section and the 'appXL.activeworkbooks.save...'
portion to get your data in Access. I do a lot of data integration from
crazy spreadsheets and that's how I do it if it doesn't import properly
directly into Access. It's usually easier to create an excel macro and tidy
things up before importing into Access, especially when dealing with dates
and numbers. Your next experience is then researching how to use this info
in an append query to add to an existing table and then delete duplicates.

Hi Maarkr,

I was able to insert the module and run it. However, it only copies the
value of the first change ID. In other words, it doesn't recognize to stop
when it encounters a non-blank in order to begin the process again. Do you
know of a further way to run this module?

Thx,

PC

well, this may help if you play around with it...I call this from a module
that copies the cells down in the A row.. then it appends the spreadsheet
data to a table...

Private Sub CopyCellsDown()
'shown as an example to run excel macro actions from Access
'fills and copies blank rows in col A

Dim appXL As Excel.Application
Set appXL = New Excel.Application
appXL.Visible = False

appXL.Workbooks.Open "S:\Auth_Assign2.xls"

Range("A2:A200").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Sheets("Assigned Totals").Select
Range("A2:A200").Select
Selection.FormulaR1C1 = "=+R[-1]C"


appXL.ActiveWorkbook.Save
appXL.Quit

End Sub

Hi,

My data table has a serious flaw, and unfortunately it is an import table.
I'm trying to fix this table but I need serious help. Here's the situation:

Change ID Change Category
1 a
b
c
2 a
b
c
3 a

As you can see, some rows don't carry the corresponding "Change ID." Is
there a way to automotically drag the change id until it reaches the next
Change ID number. For example, drag down the change ID 1 until it recognizes
change ID #2, then drag change ID 2 until it finds change ID # 3.

Please let me know if this is possible.

Thanks,