How to horizontally align columns? Is it possible with VBA?

[Eloise T]

Each week I receive a two-column Excel “check detail” file from a customer listing all the completed and paid work orders for the week based on the check they wrote.

Frequently, I have a few more WOs in (Columns E through N) [what my records show they owe] than the customer pays (Columns B through C) [what they actually paid on the check].

Currently Column O flags discrepancies, but I would like to take it one step further by aligning Columns B thru C with the Columns E thru N.

Even though I presort the two segments of data by WO number (Column B thru C) and (Columns E thru N) [what the customer paid vs. what my records show they owe], I’m looking for a VBA that will “horizontally align” the data based on WO numbers in Columns B through C with the data in Columns E through N. Currently I’m doing it by hand and there are many more than the few shown in the example. See sample pictures below and two attachments. Since Excel pulls data vs. push data, is it even possible to do this? If so, any ideas on how to do it?

Thank you for looking.

BEFORE:


AFTER:

 

[vletm]

Eloise T
Could it be like this?
There is Sub Do_It in end of Sheet1-code.

 

[Eloise T]

Eloise T
Could it be like this?
There is Sub Do_It in end of Sheet1-code.

It works perfectly with the sample I sent; however, when I run it with real data, it makes most of the real data in Columns B & C disappear. Attached is the most recent file with real data. Thanks for taking a look.

 

[vletm]

Eloise T ... rechecked ...
Your Sample file has more B&C-columns rows data than E-column ...
and that's the case!
Your Real file has more E-column rows that B&C-columns ...
I checked ... and ... rechecked ... now,
both case would be possible and
it would work 'better'.

 

[KenU]

It seems like it would be simpler to keep the payment data (columns B & C) on another sheet and simply use a formula to report the amount paid to the desired column in your primary table. I would probably use SUMPRODUCT to do this. The values would then line-up by default. It would also handle multiple payments on the same work order number.

Ken

 

[vletm]

Eloise T
... there were still possible challenges with overlappings
if same number of rows between BC and E ..
This challenge would take care with this version.

 

[Eloise T]

Thanks for your good work and persistence. I'll check it with new data hopefully Friday (tomorrow).

 

[Eloise T]

vletm,
I am curious how the previous VBA failed as compared to the most recent version?
...you said something about over-lappings? I didn't follow you train of thought.


Also, with me being a total novice at VBA, I was wondering why you set b = e and then on the very next line:

b = e
b = WorksheetFunction.Match(chk_e, .Range("B1:B" & max_e), 0)

Please explain the logic to set "b = e" and then immediately set it to something totally different.

Thanks.

Eloise

 

[vletm]

Eloise T
Previous version worked okay with the original sample file,
because there were less B&C-rows than E...-rows.
This works from end to start and moves B&C in correct E-row
(without checking is there free place or not).
Newer version had other situation; more B&C-rows than E...-rows.
If use same code then that won't work 'as well as it should'.
This needs to move E...-rows to correct B&C-row.
If You'll do same 'manually', You'll notice it sooooon - okay?
The latest version also checks, that there is free place for moving data.
If no free place, then both B&C and E-rows datas will move to end of data.
>> Need all kind of data to see/test, how do it work <<

 

[Eloise T]

Got data this week with 15 notes. After checking, it seemed to work perfectly.