Isolating Data

thecircularwriter · #1 02-01-2010, 02:56 PM

I have a report that lists individuals, dates, quantities, dollar amounts and a description. There are multiple entries for each individual and both the amounts and descriptions are also repetative so vlookup is a no go. What I'm trying to do is isolate the entries for each individual on their own sheet. What I have currently works but is tedious. On receipt of the report, I insert rows at the start of each new person so that they are assigned to a specific location. On the individual's sheet, I have a formula that duplicates the data from the assigned rows. In addition, the report flucuates in length and number of entries on a weekly basis.

I considered the IF function but again, I run into location problems as the report is a approximately 500 rows which means an IF formula to examine every entry and the person starting at row 451, will have the data that far down the on their sheet and I would have to delete the other 450 rows which is even more tedious that what I'm doing now.

Any ideas?

One other unrelated issue with this is with vlookup (as usual). I have a formula that divides amount by quantity. The result of the division is compared to a rate chart and returns a second value. This works fine for the most part but occasionally, I get an #N/A return and I don't know why. Example, $45.54 / 99 = 0.46 with no additional decimal places. The vlookp should return 0.38 when it finds 0.46 but gives me an #N/A instead. Oddly, this does not happen consistently as sometimes it works. Using this example, if I change the $45.54 to $45.55, I then get the correct answer of 0.38 which has me scratching my head.

Thanks in advance.

NBVC · #2 02-01-2010, 03:26 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Also you should put unrelated questions in separate threads....

thecircularwriter · #3 02-08-2010, 03:31 PM

Ok, here are some example files. In example one, please look at cell i103. Although the calculation to arrive at the number on the vlookup is bang on (no extra decimal points), I get the #N/A return value. If I change the value in cell f103 from $8.28 to $8.29, I get the correct answer. Don't get why this is.

In example two, I have now modified the report by inserting rows as mentioned before into 25 rows for each person to isolate the information on the individual's sheet. Aside from being tedious to do this, as previously mentioned, adding new people or deleting them is a major pain and also if I have to move some one in order to maintain an alphabetical listing. I have also allocated 25 positions for each person and if that increases, I have another major re-work to do.

Finally, I am using column M as a double check for accuracy and but that's a pain too when I reach the rows with no values and get the #DIV/0 error return. Look at the sheet for Athwal, G. versus Athwal, H.

Hope this helps in sorting things out.

Thanks

6StringJazzer · #4 02-08-2010, 08:07 PM

Quote:

Originally Posted by thecircularwriter

Ok, here are some example files. In example one, please look at cell i103. Although the calculation to arrive at the number on the vlookup is bang on (no extra decimal points), I get the #N/A return value. If I change the value in cell f103 from $8.28 to $8.29, I get the correct answer. Don't get why this is.

The situation in I103 is very strange, and I would go so far as to say it's a bug in Excel. When you change the amount to 8.29, that works because now the answer is slightly larger than 0.46 (0.4605555555 repeating) and VLOOKUP will find the largest value that is less than your number, so it matches 0.46. However, when your calculation yields exactly 0.46, VLOOKUP fails to find a match with 0.46. If you type the number 0.46 into I103, VLOOKUP works. That's why I think it's a bug--0.46 is the same number whether you type it in, or calculate it by division. I don't know how you can fix this except by a kludge, which would be to update your lookup table B2 to be 0.4599999. This does work.

I'll look at the other things as time permits.....

6StringJazzer · #5 02-08-2010, 08:25 PM

Quote:

Originally Posted by thecircularwriter

....Finally, I am using column M as a double check for accuracy and but that's a pain too when I reach the rows with no values and get the #DIV/0 error return. Look at the sheet for Athwal, G. versus Athwal, H.

In Summary!I15 you can use

=IF(ISERROR(H15),"",VLOOKUP(H15,Rates!$B$2:$C$15,Rates!$D$1))

to avoid the error. This can be copied to other cells in Column I.

shg · #6 02-08-2010, 10:13 PM

If you change the formula in value H103 to =ROUND(F103/E103, 2) it works fine.

thecircularwriter · #7 02-09-2010, 01:01 PM

Thanks, all suggestions have resolved the problem in example one; not familiar with the term "kludge" but it certainly sounds appropriate.

This brings me back to example two and the ongoing problem with isolating the rows of data for a specific individual on the summary sheet at a specific location on a separate sheet. I have tried a different approach by assigning a specific number to each person (column B on the summary sheet) and using vlookup on the individual's sheet but of course that stops working as soon nicely up until the number being looked up doesn't exist.

Is there any formula that stipulates that if vlookup cannot find the value in the range specified that it return a 0? At any rate, have now attached example 3 of this latest aborted attempt at a solution.

Thanks

Welcome to the Excel Forum

Forum Statistics