Question Locked

Excel Macro out of synch

0 helpful
18 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Would you know how to create a macro in Excel for the following situation? If not, do you know who I can check with? Dianne Garcia in the credit union works with a spreadsheet every month. When she copies the H column with 1,000 cells, she needs those cells to be lined up in sequential order to match with the same cells (side by side) that are in Column G.

Example:

45516 matches side by side when she paste?s H column, but then gets out of synch with number 45521 next to 45520. So, a macro would need to be created with an ?if? statement. She really doesn?t want to move the cells manually because there are 1,000 cells to shift.

18 replies

by Former member

Please post back if you have any more problems or questions.

by javier.martinez

Is there a way to post an illustration for excel on this forum? If it is how?

by Former member

So please post on here as much info as possible.

Please post back if you have any more problems or questions.

by javier.martinez

No answer found in last update in reference to Excel out synch fields. I hope still looking for it.. Please keep me posted for more updates in refernce to this problem.
Thanks

by javier.martinez

Any luck w/ finding a solution for the Excel Macro skipping and leaving blank spaces?

by Former member

I will keep you posted.

Please post back if you have any more problems or questions.

by thumbsup2

Is this column H on the same spreadsheet as column G that she's trying to match to?

If not, have you tried the VLOOKUP function? I don't understand why you would need a Macro when the VLOOKUP can do what you're wanting to do. Put it where she would be pasting column H, but make sure that column H, wherever it's at, is sorted numerically before the function is inserted. And, when it's done, if you don't want to keep two spreadsheets tied together, you can copy those cell and paste special (values) back over the top of the formulas so just the values remain.

I don't know. I guess we need a better description of what you're trying to do.

...

by Former member

The only thing is i do not know how i would get the image on to this site. At a guess, (and this is a pure guess), would it be photobucket, and if so how would i go about putting the image onto it?.

by Former member

Sounds like a big problem to get sorted, that is why it is taken so long. I will keep you updated, unless someone on here has the answer.

Please post back if you have any more problems or questions.

by javier.martinez

no answer yet.. I tried this but still does not work.

If the values to line up have to be exactly the same, she could use the following IF formula:
=IF(ISNA(MATCH(G1,$H$1:$H$1000,0)),"",G1)
What this formula does is to check that the value on the column G is present in the H column, and if so it will be copied. If the value is not present, the column will be blank.

I tried but still did not work

by thumbsup2

There are values in H where there are no values in G. And, that's where the copy gets out of sync.

by Former member

I sent the info to you, did you get it?.
If not then here it is for you to try out:

???

4962ea34-1008-4209-a7d6-bc7ebf45b03b
69c0a1fd-45b0-42f1-9fbf-d60688cab748
0

Run

ExcelFile

ButtonPressed

Excel

FirstFreeRow

RetrievedCol

CurrentRow

CurrentValue

RowNumber

CellValue

PreviousCellValue

None
OK
Button1
false
3

*.xls;*.xlsx
true
false

Equals

false

true
true

false
false

LessThan

Please post back if you have any more problems or questions.

by thumbsup2

The problem is that the number of items in column G doesn't match the number of items in the column H source data, where ever it is located that she's copying from.

The logic to fix it would be a piece of cake though. It might look something like this:

Initialize all of the variables. You'll need some sort of counters to know when to stop reading column G and column H source data. One would also HAVE to know what the first number in column G is, just in case there is a blank at the top of the column. Set the hold-variable for column G to the first value that WOULD be in column G, if it had something in it. I don't know where her numbering system starts and ends in column G.

Now, traverse column G, one at a time, starting at the top.

If there is a number in column G, lookup that same number in column H (where ever it's at that she's copying from) and paste it into column H of this sheet.

If the number does not exist in the source column H, leave this column H blank. Remember, the source column H must be sorted numerically, removing blanks. So, I would put a copy of the data on an added sheet in this workbook, all by itself, and sort it thereby leaving the source data alone.

If there is no number in column G, add one to the previous number in column G and use that as a lookup parameter, then do the same copy/paste.

Move down one cell in column G and loop. Keep doing this till you're done, making sure there is no extra data in H that isn't also in G (ie, blanks at the bottom of G but there is data in the source H).

---

If I had the source document names and tab names, I could recreate those workbooks/worksheets and write a macro for her. Without specifics though, anything that I wrote would have to be modified.

---

by Former member

Please post back if you have any more problems or questions.

This discussion is closed to new replies.