Aligning data column headers

Hi,
I have slowly improved with loops etc in vba, but my latest problem has me completely baffled.
I get data each week which I can concatenate to a certain degree but I get stuck on aligning the dates in row one with the quantities in the columns below. The data is imported from a Crystal report which is why the columns are slightly off.
I have attached the data sheet which is at the stage where I am stuck, and if anyone can point me in the right direction as how to continue and align the dates with the data below using vba, it would be much appreciated.
DataDump.xlsx
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Just to clarify:

[A1] "Item No" is OK.
[C1] that is presently "Descripti"; you would like that to be moved to [E1].
[F1] "On Hand" should be in [G1].
[I1] "11/May" should be in [J1].
[K1] "12/May" is OK (as data exists in the same column).
[M1] "13/May" is OK (as data exists in the same column).
[O1] "14/May"; same as above.
[Q1] "15/Day"; ditto.
[R1] is presently "18/May", but that is the column heading for [S1].
.
.
[AK1] "08/Jun" goes back one column to [AJ1].
[AM1] "15/Jun" also goes back one column to "[AL1].
.
.
.
Finally, [BB1] "03/Aug" should be moved to [BA1].

BUT... that means you have one more column heading that you have columns of data!

Column [BC] has values, but no heading.

Is that correct?  I suspect not.

Please can you check the attached example, & then clarify what is your desired result?

Perhaps a "before" worksheet & "after" worksheet in an additional attachment would help.

Thank you.
0
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks a lot for looking at this.
I have attached two more files which may make it clearer.
The original data from Crystal and then I have pasted that into the macro-enabled workbook which I am currently working on 'Current Project'.
As you will see in the code window of the current project, I have commented-out the lines not needed for this example.
If you hit the button on sheet 1, the code should run the first step, but as I say, it's lining up the quantities to the dates that's I'm stuck on.
Once again, thanks for looking into this
First-dump-from-CrystalReport.xls
IQMSdump.xlsm
0
aikimarkCommented:
I would work towards removing the unnecessary spaces.  That should go a long way to helping you align the data
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for the comment "aikimark"
I tried that with vba, but I can't figure out how to get the vba to differentiate which blank spaces need to be there on any given row, because if I remove the blank cells from some of the rows it brings the quantities too far forward for that particular item.
0
aikimarkCommented:
CR hasn't done you any favors on this one.  Is it possible to export this as a tab-delimited or CSV file?
0
aikimarkCommented:
These two routines will tighten your data.  Invoke the DeleteSpacerRows() first.
Option Explicit

Sub DeleteSpacerRows()
    Dim rng As Range
    Set rng = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp)
    Application.ScreenUpdating = False
    Do
        If rng.RowHeight < 5 Then
            Set rng = rng.Offset(-1)
            Application.StatusBar = "Deleting: " & rng.Offset(1).Row
            rng.Offset(1).EntireRow.Delete
        Else
            Set rng = rng.Offset(-1)
        End If
    Loop Until rng.Row = 1
    Application.ScreenUpdating = True
    Application.StatusBar = vbNullString
End Sub


Sub RollupRows()
    Dim rng As Range
    Dim wks As Worksheet
    Dim lngColCount As Long
    Set wks = ActiveSheet
    lngColCount = wks.Columns.Count
    Set rng = wks.Cells(wks.Rows.Count, 1).End(xlUp)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Do
        If rng.Offset(0, 2).End(xlToRight).Column = lngColCount Then
            Application.StatusBar = "Rolling up: " & rng.Value & " -- Row: " & rng.Row
            wks.Range(rng.Offset(0, 2), rng.Offset(0, 2).End(xlToRight)).Delete xlShiftUp
            wks.Range(rng.Offset(1), rng.Offset(1, 2)).Delete xlShiftUp
        End If
        Set rng = rng.End(xlUp)
    Loop Until rng.Row = 1
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = vbNullString

End Sub

Open in new window


After cleaning the data, I see that there is a data-alignment problem.  There are ten columns with
11/05		12/05		13/05		14/05		15/05	18/05		19/05			20/05			21/05			22/05		

Open in new window

headers, but only nine columns of data below them.

The good news is that all of those mis-aligned column rows, there is data in the (merged) BC:BD columns.  If you could establish a pattern, it might be possible to shift the data to the left on those rows.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for the pointers and the tip on removing spacer rows.
I can now see a way to finalize this and get it up and running.
Thanks for your help
0
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.