search, define and compare

[asddsa88]

Hello,

this shouldn't be too hard but I am stuck!!

I have a dataset with multiple entries.

Each entry is formed by 2 rows, the first row defines the entry name and the second row defines the values (1 value in each column) associated with that name.

example:
entryname = abc
entryvalue = 1,2,3,4,5


I need to create a search prompt that:

has 2 boxes, box 1 and box 2.

When I enter the search queries in box 1&2, the function searches for entrynames and finds the 2 unique entrynames that I am looking for.

When the macro finds the entryname that I need, it shifts 1 row down and selects all the entryvalues and defines them as range.

Then I need to compare the 2 ranges and find only the values that are present in both ranges.
(I was thinking to use hlookup or maybe conditional formatting for this, but I am not completely sure!)



example:

search query:
searchbox1="def"
searchbox2="ghi"

dataset:

entryname = abc
entryvalue = 1,2,3,4,5
entryname = def
entryvalue = 1,2,3,4,5
entryname = ghi
entryvalue = 1,2,3,4,5

the macro finds the 2 entryvalues associated with def and ghi and returns 2 lists in full because in this case they are exactly the same (1,2,3,4,5)

thanks in advance for any suggestions!

 

[asddsa88]

any advice?