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

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!

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

D14

Please Login or Register  to view this content.

Try this and copy across

D14

Please Login or Register  to view this content.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Thanks for sharing that. The reason we - and most other forums - insist that you share links to other sites you asked the same question, was so that we don't all waste our time working on something that may already have been resolved some place else...Im sure you can understand

i was going under the premise that "generally" on the internet websites dont like when you make links to other similar sites even if it would be useful

That also makes perfect sense when you put it that way, good point