The goal of my project is to calculate weekly demand for a certain type of server. The primary obstacles are a purchasing database that has a wide range of positions for user inputs. I have attached a file so you can see the formulas on the left I came up with. Unfortunately some of the references did not carry over, but you can see what I was trying to do with the indexing. I will end up pivoting with the 'Insert RAM', 'Short Desc', and 'Model'. The model I was planning on getting a list of actual models since many of those are not qualified models and doing a vlookup comparison to fill in those. Regardless it would be a similar if statement formula assuming I can find a consistent enough pattern of placement on all of the entries. I just got kind of lucky that the RAM amount was almost never on the bottom row. I put in this formula in the B column to make it work and this is what I am worried about:
=IF(AND(D2="Top",ISERROR(VALUE(E2))),IF(AND(NOT(D3="Bot"),NOT(ISERROR(VALUE(E3)))),F3,IF(AND(NOT(D4="Bot"),NOT(ISERROR(VALUE(E4)))),F4,IF(AND(NOT(D5="Bot"),NOT(ISERROR(VALUE(E5)))),F5,"MORE"))))
I am working with a database that has multiple lines per entry and an inconsistent entry point for each entry. There are about 83K entries and I do not have access to edit the database, I just have to work with it. The entries take up about 5-15 lines depending on how the user input the data. The data I need finds itself randomly placed in between about 5-6 different cells. I solved the problem with a nested IF statement that is of course enormous.
I was just thinking there HAS to be a formula out there that could have made this easier. On top of that every time I change the filter it causes the program to freeze for minutes at a time without showing the processor progress. This makes me think that the formulas are inefficient.
After solving one of these problems with a nested IF statement, I am starting to worry about the amount of processing time to verify another component of this which will take an even more complicated IF statement.
I am going to need to recreate this on a much larger scale and I would like to have some formulas that are not so complicated and laggy.
Bookmarks