index match multiple results

[rrocker1405]

Hi,

I'm trying to pick up multiple results using index and match and from that create another index and match to pick multiple results. I dont know if there is a simpler solution for this.

I'm also not sure how to do if there more than 10 results to show up in one cell and then replicate the same to pick individually. Attached is the sheet for reference as sample.

Thanks in advance.

Kind regards,
A

 

[NARAYANK991]

Hi Anand ,

Using formulae it is either very cumbersome or impossible to concatenate results.

Secondly , if the relationship between the items in column B and those in column C is one to many , even this approach will not work.

See the file.

Narayan

 

[rrocker1405]

Thanks for this Narayan. This is something I can use as reference and work around with i guess. could this be done using vba?

The reason why I'm asking is the file that i have got is more than 20000 entries and have to do loops for about 15 fields like this and that also increases the file.

Thanks in advance.

Kind regards,
A!

 

[NARAYANK991]

Hi Anand ,

If you can upload a representative file , which has data for the 15 fields that you have mentioned , with data extending to at least a hundred rows , if not more , then we can understand the real complexity of the task.

Narayan

 

[rrocker1405]

Hi Narayan,

since there are sensitive information i wont be able to share information. However, i have tried to explain my exercise that I'm doing right now in the attached spreadsheet with some very high level sample data and I have also included a diagram which provides a visibility of the tree model for easier reference.

sheet 1 includes the fields and sheet 2 includes the looping that im trying to create with additional information. not sure if I have answered all ur question but would be a great helpf if you can suggest an easier way.

Many thanks and thanks in advance.

Kind regards,
A!

 

[John()]

Found this formula on the Chandoo blog few days ago in one of the write ups ... it has potential to solve your problem ...
I have attached a file with it in action in relation to your problem

well from your original file ..... see you posted again as i was posting so need to look at that file now as it seems more complex lol ....

 

[NARAYANK991]

Hi Anand ,

I assume that what you want to show is what you have entered in the range D20:H37.

The ideal way to show this would be a TreeView control , but it involves VBA.

Misra had written VBA code to display a cell-oriented tree some time back ; see the thread here ; Misra's file is in his last post.

http://chandoo.org/forum/threads/reporting-hierarchy-from-the-sample-data-attached.15844/

If you can rearrange the relevant data in the manner in which his code expects , then you may be able to use his code as-is.

Narayan

 

[rrocker1405]

Hi Narayan,

Thank you for sharing this thread which i think should help in a way however, i wasnt sure of the formula that has been used in column d (file of misra). does it try to pick up the reporting senior manager information? if yes, I'm not able to see the relation of the value it picked based on cell a2 and b2 [in sheet2].

=IF(INDEX(IF($B$2:$B$1603<>"",$B$2:$B$1603),SMALL(IF(ISNA(MATCH(IF($B$2:$B$1603<>"",$B$2:$B$1603),$A$2:$A$1603,0)),ROW($B$2:$B$1603)-ROW($B$2)+1),ROWS(D$2:D2)))=FALSE,"",INDEX(IF($B$2:$B$1603<>"",$B$2:$B$1603),SMALL(IF(ISNA(MATCH(IF($B$2:$B$1603<>"",$B$2:$B$1603),$A$2:$A$1603,0)),ROW($B$2:$B$1603)-ROW($B$2)+1),ROWS(D$2:D2))))

could you please explain, i think i should be able to use the vba script as i understand it uses the row number and index formula to create the loops and presents the hierarchy.

thanks in advance.

Kind regards,
A!

 

[NARAYANK991]

Hi Anand ,

When we talk of code , we mean VBA.

I did not refer to the formulae.

Narayan

 

[rrocker1405]

Hi Narayan,

Thank you for the clarification. I understand that you were referring to VBA only when you referred to as code but when you look in to the code of misra it picks up the column D & H, these columns have values to pick up basedon the formulas index as stated in my earlier reply which is why I asked the clarification.

Having said that I have attached the sheet with the hierarchial values that i'm working presently with. Sheet 1 > columns in grey are the base data and columns in blue is the parent child data list and column green is the trace from to trace to data and this follows the following loop.

Objective > user requirement > System requirement > attribute > branch

Objecive: parent
User requirement: child and parent for System requirement
System requirement: child to user requirement & Trace from an attribute
Attribute: traces to a system requirement & parent to branch
Branch: child of attribute

I know i'm asking too much but I would be grateful as always if you could let me know if it can be achieved using a vba script.

any help would be very beneficial.

Thanks in advance.

Kind regards,
A!

 

[NARAYANK991]

Hi Anand ,

Sorry , I have not gone through Misra's file , and I did not know the contents. I thought he had achieved the output using only code.

Why don't you interact with Misra ? either he may modify his earlier code to take into account your data layout , or using formulae , the data can be rearranged to suit the layout the code is looking for.

The point is to select that approach which is easiest to implement , and cater to your present and future requirements.

Narayan

 

[rrocker1405]

I absolutely agree with you on this Narayan. I dont know if I can send in an message directly to misra?

Thanks once again.

Kind regards,
A!

 

[NARAYANK991]

Hi Anand ,

Sure , why not ? As long as your requirement is not urgent , I am sure he will help.

Narayan

 

[rrocker1405]

Thank you Narayan.

Kind regards,
A!