Finding and inserting value from previous list.

[Mohohunter]

First off a disclaimer. I have also posted this same question on a different forum but after three days I haven't received a reply yet so I thought I would branch out. My previous post is at http://www.mrexcel.com/forum/excel-q...ious-list.html Please feel free to post a reply on either forum and I will be sure to update both as solved when it is.

I appreciate in advance your help. I will try to give as much information and background as I can.

The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example
A B C D
Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0

I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...
A B C D
Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300

As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.
Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...
A B C D
Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300

My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.

[FDibbins]

Hi and welcome to the forum

1st, thanks for including the link to the other forum

When you update the data, is the old data kept, or overwritten?

[Mohohunter]

Thank you for the quick reply. When I update the data all the old data is kept( I need to reference it later). I just move farther down the spreadsheet.

[gak67]

An example file would be appreciated.

A VLOOKUP sounds like what you need - something like:

Please Login or Register  to view this content.

That is assuming the previous list is in a separate tab. You may be able to use an INDIRECT function to reference the previous list tab easily, but we don't know where you keep the previous list without an example.

[gak67]

Given your reply in post #3 an OFFSET or INDEX function might be better suited, rather than an INDIRECT function, but an example sheet would allow us to answer more fully. To upload a file click on Go Advanced (bottom right) and then on the paper clip.

[alansidman]

See attached.

[Mohohunter]

Ok give me a few minutes. As the spreadsheet is full of customer information I will need to make a sample sheet with edited information.

[Mohohunter]

Alansidman That formula did not seem to work. Maybe i did something wrong. It just changed my values in column C to $0

[gak67]

Please Login or Register  to view this content.

will give you what you want, but you will have to set the ranges each time manually unless you have some way of identifying the previous list from the other data on the sheet, eg a date of when the data was added, or a sequential number.

[alansidman]

Without seeing your data, I cannot explain why it does not work for you. It works in my example. Are your names consistent from table to table. Unique identifiers usually work better in situations like this as names can be misspelled, comma placement and space placement inconsistent. Once you load your data, will relook at this unless some of the other forum members beat me to it.

[Mohohunter]

Sample Sheet.xls


Ok here is the attached file. All the names would be consistent because they are made by my dealer software. I print them to .XPS and then use a conversion site to .XLS

[gak67]

This in C9 of your sample sheet works

Please Login or Register  to view this content.

[gak67]

So which columns are you adding in your more advanced example sheet? And which column does the result go in?

[Mohohunter]

I am wanting to add Column C + D and result it in Column C of the next list. This serves as a running total. I need to see what was previously paid, what was paid for that period and enter that as the new previously paid in the following list. Theoretically C+D should always be the same as B but there are a small percentage of the times when that is not true.

[gak67]

Try this:
Sample Sheet.xlsx

It looks at the last instance of the name, so if the person leaves the list and then comes back it will find the last instance of their name above.

[Mohohunter]

Ok so now may be bad time to admit this but I am using Open Office instead of Excel. When I open that file my whole second set on Column C & D reads #DIV/0! I thought that I would just be able to change the ":"'s in formulas to ";"'s but it seems not to be the case.
I am very sorry it seems that the function "iferror" is not a function of Open Office

[gak67]

I'm not that familiar with Open Office (I tried using it once, but I was so used to Excel that I found the differences frustrating), but it's more likely to be the LOOKUP function that does not translate to Open Office. If it is the IFERROR function though try:

Please Login or Register  to view this content.

[Mohohunter]

First off thank you for not getting upset at me as I am very grateful for all your help. It seems that you are on to something at that not give the same #DIV/0! error. After I changed out all the commas and replaced them with ; it shows $0 in every space in column C. You have been great. I will take this formula and see if on the open office forum if they know how to make this work.

[Mohohunter]

Ok gak67 I was finally able to open your sample sheet in excel instead of open office and the results in all of column C is just $0

Try this:
Attachment 318620

It looks at the last instance of the name, so if the person leaves the list and then comes back it will find the last instance of their name above.

[gak67]

Excel 2003 does not have the IFERROR function. Try this in C56 of the sample file you uploaded, and copy it down.

Please Login or Register  to view this content.

[Mohohunter]

Ok I will try that. The version of Excel I'm using is a trial one and only allows me to view files but not edit. It will probably be until tomorrow before I am able to paste that formula in. Alternately you could put the formula in the spreadsheet and upload it like before so I can test it.

[gak67]

As requested.
Sample Sheet (1).xlsx

[Mohohunter]

Ok Gak67 I opened this file today and it does indeed work. I copied the second list and pasted it down and it did indeed grab the information from the previous list and not the list above that. I have yet to insert this formula in my original document but it looks like everything will work. Again thank you so much for all your help.

As requested.
Attachment 318657

[Mohohunter]

So I didn't want to start a new thread for this since it is related. I am hoping that I can also get another formula for the same sheet. I am wanting a formula that will Delete the row when C=>E

[gak67]

There is no formula that will delete a row, You need a macro/VBA for that.