Complex Formula Algorithm (Variable size array created from index/match/adress returns)

[XionicFire]

Hi, im not sure how to explain the situation so bear with me and thank you for looking,


Im trying to create something extremely complex, ive searched over the internet cant find anything similar, so i decided to make it in parts, dissasemble the formula into 6 different parts, then find each part individually on the internet, for the most part i had good success until i got to the point where i need to create a variable sized and location array, ill explain more in formula format:


Ok so basically goes something like this:


Im trying to create a data parser to parse an xml dump file in order to process all the data, search for specific variables in the table, then within the results of that table, search for specific strings, and then return the values of those strings on another page, effectively delivering a clean "output" so to speak without all the extra useless XML code so that the info can then be fed into a spreadsheet for other uses.


The original data in the XML table looks something like this:


ID lvar Data
65464 type B002344
65464 flags 2131898
65464 brand Airlink101
65464 model AR504
65464 tr69 Yes
235422 type C002311
235422 flags 33123144
235422 brand BKTR
235422 model BK20031


The ID area is basically the unique unit or product identifier, and the lvars are the products different features, data is the relevant data from those features.


So essentially what want it to do is:


First search the entire file on column A for Similar ID Numers, from that somehow parse and identify them into one array (say it found 10 entries contanining the same ID number in range $:R37:R48), the lvars are not always the same, some have more and some have less so a match+10 from the first id "hit" wouldnt work, essentially it needs to generate an array that varies in size from product id to id, (and if possible that can fetch info even if its not sequential (random order), IE $R37:$R40 - $R44:$R7), once thats done (and this is the part ive already got working and nailed down with the following formula,=INDEX($D:$H,MATCH($C15,!$H:$H,0),MATCH(G$12,$D$12:$H$12,0)) it needs to find the data assigned to every lvar string and parse it in a different format in another worksheet.


This part as i said i already got working fine, the problem im getting is with the original parsing of ids, ive tried several methods to no avail, the most promising method ive found so far (but sadly is vulnerable to corruption if the data is not sorted correctly(randomly ordered), which i can fix with a quick pre-sorting before pasting the info in the worksheet) is to use basically the same formula i use to parse the lvars but instead of telling it to search the whole array i tell it to search from the first match found of the ids to the last using these 2 formulas:


This finds me the first cell where the ID was seen: =VLOOKUP(E12,!$D:$D,1,0)
And this finds me the last cell: =INDEX($D:$D,MATCH(E12,$D:$D,0),1)


From that i could theoretically construct a "variable sized and location array" however every method ive tried to extract the exact adress location from those formulas has failed.


If i try: =ADDRESS(MATCH(E16,D12:D34,0),1,1) I get something like $A$5 even tho its blatantly obvious the data is somwhere around $R50 and nowhere near $A$5, the match adress return seems to be relative to the location inside the array and not the entire worksheet.

Trying to select the entire worksheet as a search area only returns a N/A Error so thats also out of the question :/


So basically theres my two problems, if anyone has a good idea how to do this, or simply how to get a simple correct worksheet relative adress return for the information im trying to retrieve i would be most apreciated, with the latter i should be able to devise a solution for the variable array!


Thanks for the responses in advance!

 

[XionicFire]

Ah, your answer makes perfect sense, i understand the difference now, IFERROR checks for multiple error values, while IFNA just checks for IFNA, the 1/1 just prevents integer divides by 0 to avoid DIV errors.

Thanks for clarifying it!

Im still doing testing but with each change taking several hours its slow work haha.

Thanks for all the assistance.