Problem Extracting Data From A Generated List

[Saruman]

Having a problem trying to use MATCH, INDEX and SUMPRODUCT to produce a list
from some data.

In row 1, I have Business Process Numbers (BP's) in this format:

1.01 44.01 46.02 46.03 46.06


which start at 1.01 and at the moment go up to 60.27. There are currently
146 BP's.

The column under each BP has a list of Attribute numbers from the range of 1
to 1300 and this may very well expand in the near future. Due to the amount
of Attributes, these obviously have to be placed Vertically. The attributes
are arranged in numerical order in each column but there may be duplicates
and the length of each column is not fixed. BP 44.01 has 0 attributes, BP
46.03 has 29 attributes.

I need to produce a list that will give me the BP's associated to an
Attribute Number so i can automate a copy process.

For instance, Attribute 14 is associated with BP's 46.21 and 46.22. I would
need to be able to see the following:

Attribute BP1 BP2 BP3 BP4
16 11.10 46.02 46.03 46.30
17 46.21 46.22


I would be eternally grateful if someone could assist please.

 

[Bernard Liengme]

I followed this right up to:

I would need to be able to see the following:
Attribute BP1 BP2 BP3 BP4
16 11.10 46.02 46.03 46.30
17 46.21 46.22

What is BP1, BP2
Where is 14 in this output ?
Where did the numbers other than 46.21 and 46.22 come from?
best wishes

 

[Saruman]

Thanks for your quick reply.

I would need to see a table that has the attribute number in the left column
with the associated BP's where it was found. The BP1 etc is the associated
BP where that attribute number was found. It wouldn't need to have those
headings.

ie: Attribute 16 found in BP 11.10, 46.02, 46.03 and 46.30
Attribute 17 found in BP 46.21 and 46.22

Attribute 14 was an example that appears only in BP's 46.21 and 46.22.

The other numbers are Processes that are associated with those Attribute
Numbers in the actual file. I could send you the file if needed.

Saruman

 

[Bernard Liengme]

Sample answer sent to OP's private email

 

[Saruman]

Bernard,

Thank you for your time, I have been out of the office since Thursday and
have just got back.

The mail address that I use is not real as my proper email address has been
harvested previously and I am fighting masses of Spam. Where did you post
the reply to please. If needed, could you please send to
(e-mail address removed) after removing the NOSPAM.

Thanks.