New Data Problem

[ZackG]

Hi all,

I have posted many times, but maybe this time is the charm.

I am having trouble with users inputting text next to data that is calculating. The problem happens when new data gets pasted over the old data. New rows are added, leaving the users' text to be mis-alligned with the wrong calculations.

Basically, new rows are added when the new data is pasted, but the users' text doesn't stay with its original calculated row. Is there some formula or macro that can fix this problem?

I have attached an example workbook, as well as the actual workbook being used.


Appreciated in advance,



Zack

[6StringJazzer]

I am looking over the explanation in your sample files. I am sure that this situation is simple but I am having trouble getting the explanation. Here are my questions in between the steps you listed:

1. Data comes in (put in "Data" Sheet)
2. Data is then calculated on ETC sheet

What updates are made on the ETC sheet so that it refers to the new data? Are rows added, or are there any updates to existing rows? How do you determine which row in ETC should refer to which row in Data?

How is "New Data" used?

3. Salesmen input comment on these calculations on the ETC sheet
4. New data comes in
5. New data in copy/pasted over "Data" sheet

Is the new data added as new rows, or is existing data overwritten? What types of changes occur that make the references in ETC refer to the wrong data?

6. New data is calculated in the ETC sheet
7. Salesmen input their comments in the updated ETC sheet and can also change their previous comments

Aren't steps 4-7 just exactly the same as 1-3? If not, what's the difference?

[ZackG]

Hi 6StringJazzer,

The existing rows do not get changed once they are put into the workbook. The only thing that happens is that rows are added all the throughtout the data.

Existing data is then overwritten with the "New Data", hence having new rows. The problem is that once this happens, the comments column does not line up to its original ID# anymore...

Step 4-7 are the exact same are the same as 1-3, just with new rows in the "ETC" sheet.

Hope this clears things up.


Thanks,


Zack

[ZackG]

Please Help

[6StringJazzer]

I'm afraid this is contradictory. You say

The existing rows do not get changed once they are put into the workbook.

but then

Existing data is then overwritten with the "New Data"

So existing rows do get changed! Here is how I would explain this, tell me if I've got it:

You have a whole bunch of data in the "Data" worksheet. Then all of that existing data is wiped out, replaced by an updated set of data. The updated data set includes all of the previous data, but also includes new rows of data that are interspersed with the previous data. Therefore the references from sheet ETC now refer to the same physical rows but those rows are now occupied by different data.

Here is one way to attack this. I have made these changes in the attachment to illustrate. It is how I would do this if it were mine, although a VBA solution is also possible.

First, you need to identify data that can be used as a unique key to identify rows. In this case I would guess that is BAAN. Is that guaranteed to be unique? Now, copy the entire column A in ETC, and then Paste Special, Values, back into column A. That will lock those BAANs into those rows, no matter what happens on Data.

Next, replace all the direct references to Data with VLOOKUP formulas. That will completely break the dependency as to what row the corresponding data is in in Data. Instead of linking to a specific row, we're looking up the data using BAAN as an index. See my attachment for details.

This next part may be tricky. When new data is added to Data, identify which rows are new, and add those BAANs to ETC Column A. Then copy all the formulas down as many rows as needed. The new data will now show up in ETC. You can do this by using conditional formatting or a flag in a new column to identify BAANs on Data that don't exist on ETC and then copying them manually. Alternatively you could write some VBA to do the same thing.

You will be able to freely sort either ETC or Data without impacting the other sheet.

It is beyond me what is going on in columns ETC!AC:AI. And I ignored everything to the right of AI because it looks like experimentation.

[ZackG]

You have a whole bunch of data in the "Data" worksheet. Then all of that existing data is wiped out, replaced by an updated set of data. The updated data set includes all of the previous data, but also includes new rows of data that are interspersed with the previous data. Therefore the references from sheet ETC now refer to the same physical rows but those rows are now occupied by different data.

Yes, that is exactly it. You have it down to a tee.

First, you need to identify data that can be used as a unique key to identify rows. In this case I would guess that is BAAN. Is that guaranteed to be unique?

Yes, it is guaranteed to be unique

When new data is added to Data, identify which rows are new, and add those BAANs to ETC Column A. Then copy all the formulas down as many rows as needed. The new data will now show up in ETC. You can do this by using conditional formatting or a flag in a new column to identify BAANs on Data that don't exist on ETC and then copying them manually. Alternatively you could write some VBA to do the same thing.

It will be too tedious to identify any new rows...Is there any other way, such as a macro that could identify any new row in the data sheet?

Thanks so much,
Zack

[ZackG]

Hi Again,

It looks as though, in your updated spreadsheet, Column K (Warranty Month Total) is not functioning, as it is showing zeros in the whole column. When I go back to my regular spreadsheet, there are values (other than 0) in the cells of column K....What do you think is wrong there?


Zack

[6StringJazzer]

It will be too tedious to identify any new rows...Is there any other way, such as a macro that could identify any new row in the data sheet?

Well, yes, you are right. And it would not be difficult to write a macro to do that. I'll be able to do that, hopefully within the next few hours.

It looks as though, in your updated spreadsheet, Column K (Warranty Month Total) is not functioning, as it is showing zeros in the whole column. When I go back to my regular spreadsheet, there are values (other than 0) in the cells of column K....What do you think is wrong there?

I made an error, one of the Qs should be a U. I fixed it in the version I'm working with and will upload when the macro is done. If you want to try it yourself, note that the order of the references in the formula should be

U U Q Q
U U Q Q

[ZackG]

Ok great. Thanks, Ill try to do it myself and will match it up with your updated version.

Zack

[6StringJazzer]

Here is a version with the corrected formulas and also a macro to add newly found rows to ETC. Even if this format is different than your production workbook, the code should still work as long as the BAANs are in column A in both the ETC sheet and the Data sheet.

Note that for my VLOOKUP, I have set up a named range for the data on Data. This allows the formulas to be a bit more readable, but you have to make sure it will match your actual Data layout.

[ZackG]

Hi Jeff,

Ok, so I see that it recognizes if there are new rows added. However, they do not show up if I paste them into the "Data Being Used" sheet...How can this be done?


Thanks,

Zack

[6StringJazzer]

...they do not show up if I paste them into the "Data Being Used" sheet

What does that mean?

Here is how it should work. Paste a pile of data into Data (in this particular file it is Data Being Used). Push the button on ETC. Any new BAANs are added at the end of ETC, and formulas are copied down for the new row.

What are you doing and how are the results any different than what I just described?

[ZackG]

Hi Jeff,

I think I figured out the problem. There were about 600 rows that just had 0 as their BAAN. I deleted them and I believe all is in order!

Thanks so much you were the only who actually provided proper help after a month of trying to fix the problem!

i will get back to you if I have any other quesitons after testing it out for a while.


Thanks again,
Zack

[ZackG]

Hi Jeff,

Quick question, how did you put that Refresh Button up so I can click it?
I would like to know for another issue I am having.

Thanks,
Zack

[6StringJazzer]

In Excel 2007, to create a button, click on the Developer section of the ribbon, and then click Insert. A menu of icons will open; under ActiveX controls*, click on the button icon and then you can draw a button anywhere on the worksheet by clicking and dragging. You will also notice that Design Mode becomes active. If you double click on the button, another window will open up for code development. If you double click on the button I provided, you will see the code I wrote for it. If your button is named buttonRefresh, then if you write a Sub with the name buttonRefresh_Click that Sub will automatically be called by Excel when you click on the button.

*There are also Form controls, but ActiveX are the newer version and I prefer to work with them because they are easier to deal with in VBA code.

[ZackG]

Hi 6String,

I am still having issues getting the button to perform the task when clicked...I have spent over an hour now...Can't seem to get it.

I'll post the spreadsheet, I'm sure it's a quick fix.

Thanks,
Zack

[6StringJazzer]

Your code uses the same name as my code for the button, but the name of your button itself doesn't match. You also have a code problem that prevents compilation. Here's what you need to do:

Open your file. Hit ALT-F11 to open the code development window. Double click on the left side on the ETC worksheet, if it's not already selected. You will see the code for this worksheet on the right side. You have a duplicate declaration at the end of the code; remove this code:

Please Login or Register  to view this content.

Go back to your Excel file, and select the ETC worksheet. Click on the Developer block on the ribbon, and select Design Mode. Right-click on the button you created, and select Properties. On the first line, it says CommandButton1. Overwrite it with buttonRefresh. Click on Design Mode again. Click the button to see if it works.

P.S. There is no need to PM me when you make a new post to this thread, I see thread updates in my Control Panel and also get email updates daily.

[ZackG]

Sorry about that. Will stop PM-ing you.

I have gotten it to work. Thanks for the help.

Last question, where does the DataLookup function come from? Is it a macro?

I am trying to get it to run on my "real" spreadsheet, and isn't recognized by the workbook.

Thanks for the help, in advance.


Zack

[6StringJazzer]

Last question, where does the DataLookup function come from? Is it a macro?

I am trying to get it to run on my "real" spreadsheet, and isn't recognized by the workbook.

I apologize that I forgot to explain that.

DataLookup is a named range. Open my version of the workbook, and go to A1 in any worksheet. In the upper left corner, just above the column header A, is the address box, where you should see "A1". If you click on the dropdown, you will see a list of named ranges, including DataLookup (not sure if there are any others). You can click on it, and it will act as a "go to" to highlight that data. I used a named range because it makes formulas more readable, and if you have to change a range you can just change the definition of the name instead of changing a quidzillion formulas.

To create your own named range, highlight the range you want to name, and then type range name into the same little box I mentioned above. That's it.

You can also add, remove, and update ranges by clicking on Formulas, then Name Manager.

For more info search Excel Help for "Use names to clarify formulas".

[ZackG]

Hi 6String,

I have named the range and placed all the VLookups. However, when i click the button, it tell me that I have 655 new rows, when in fact I dont have any. Even if I highlight the "new" rows and delete them, and re-click the button, it still tells me that there are 655 new rows.

I will post the workbook I have to show you what I mean. Please help in any way, shape or form.

Thanks for the help. Obviously, without you, I'd be nowhere!


Zack

[6StringJazzer]

You missed one very important part of the instructions.

...copy the entire column A in ETC, and then Paste Special, Values, back into column A. That will lock those BAANs into those rows, no matter what happens on Data.

Also you had thousands of hidden rows in ETC after about row 650. I don't know what the intention was there. It didn't look like anything with real data.

When I cleaned up those two things, I ran it and it told me that 27 new rows were found.

[ZackG]

Hi Jeff,

I have done what you asked but have encountered another little obstacle.
Once I have pressed the button, it says there are 27 new rows. Which is all good. The problem is when I re-sort them back into the list of BAANs, none of the data that corresponds with these new BAANs shows up.

It leaves the rest of the columns blank. I would not like to drag down the formulas from each column manually, since there are many columns which would make it quite tedious. Is there a way around this?


Many thanks,
Zack

[ZackG]

I tried to attach the file, but the Zip file is bigger than 1000KB...So it wont let me...

Not sure what to do about that.


Zack

[6StringJazzer]

I will try to replicate what you described with the most recent version I attached. If I can resolve I'll post an explanation and/or reduced-size attachment to illustrate. I am thinking that for the purposes of this thread we shouldn't need such a big file. I will not have time to look at this until much later today (don't know what time zone you are in).

[ZackG]

Hi Jeff,

I am in the Eastern Time zone. Please, take your time with it. I am in no rush.
Again, thanks for your help.


Zack

[6StringJazzer]

I was able to sort, and didn't see the problems you described. Perhaps there was some change you made after that?

Also, there was something I didn't catch in the previous cycle, but it's not related to this problem. In cell B2 of ETC of my original suggested solution, I used the formula

=VLOOKUP($A2,DataLookup,COLUMN('Data Being Used'!B$1),0)

In your solution (which adds an extra header row, so this formula is in B3), you used

=VLOOKUP($A3,CHANTALDATA,COLUMN(B2),0)

This should not cause the problem you are describing but will mess things up if you delete rows (I discovered the problem after deleting rows). The original formula is robust against adding columns to the data sheet, although realistically that may not be an issue. To keep things as simple as possible you could change it to

=VLOOKUP($A3,CHANTALDATA,2,0)

This will always work if the BMRef is always the second column of range CHANTALDATA.

[ZackG]

Hi Jeff,

Thanks for the heads up.

In terms of my original issue, could you please send me excel spreadhseet you are using, so I can compare the differences between the two?

Thanks,
Zack

[ZackG]

Hey Jeff,

I will attach a spreadhseet that shows the problem I am experiencing.
It occurs from rows 631 to 658 I believe. The BAAN shows up, but the rest of the data does not...

Thanks,
Zack

[6StringJazzer]

Your attachment is just values, and just from the ETC worksheet. It's impossible to diagnose without at least seeing formulas and the referenced data. Ideally I would be able to follow the same steps you did to get this result.

I've PM'ed my email address. If your file has gotten too big to attach here, send it to me by email and I'll respond in this thread.

[ZackG]

I have emailed you the attachment.

[6StringJazzer]

You have a lot of garbage in the rows after 630 all the way down to 857. I deleted all of that and everything works fine. Your data is contiguous so the VBA uses the range UsedRange to figure out where the data ends, and the garbage data may be causing some trouble. If you delete from row 631 to the end of the sheet and then run the macro you will find that the 27 new rows are added correctly.

[ZackG]

Thanks for the help Jeff!