Grouping Items by Arrays
 

I'm working with what feels like inverted hierarchy here and I need
some help in linking topics with arrays.

For the sake of illustration; I have 500 products. I run tests on each of
these products before they are approved for sale. In fact I run a group of
tests on each product. I don't run the same group-of-tests on every product,
but I do use a test-group on more than one product.

Example

Product A - gets tests 1, 3, 4, and 7
Product B - gets tests 2, 3, 4, and 9
Product c - gets tests 2, 3
Product D - gets tests 2, 3, 4, and 9
Product E - gets tests 2, 3, 4, and 9
Product F - gets tests 1, 3, 4, and 7
Product G - gets tests 1, 3, 4, and 7

I want a way for Excel to "recognize" the contents of the array "1, 3, 4, 7"
and associate it with Products A, F, and G. Similarly I want Excel to
"recognize" the array "2, 3, 4, 9" and treat that in a manner such that it is
associated with Products B, D and E.

The test-groups are easily envisioned as subordinate to the Product. But, I
want to make the test-groups one rung above the Product in terms of
hierarchy; grouping Products by test-group.

There are too many variations of test arrays and too many products to do
this manually or visually in less than a day. And as with anything tedious
and manual, there is opportunity for error by doing it that way. Can Excel
handle this task with ease? I hope.

Thanks

Grouping Items by Arrays
 

How is the data organised?

With your example:

Product column A ?
Test ID columns B to E ? e.g, 1 ,3, 4 and 7

and maximum tests per product?

And output to look like ...?

"In need of enlightenment" wrote:

I'm working with what feels like inverted hierarchy here and I need
some help in linking topics with arrays.

For the sake of illustration; I have 500 products. I run tests on each of
these products before they are approved for sale. In fact I run a group of
tests on each product. I don't run the same group-of-tests on every product,
but I do use a test-group on more than one product.

Example

Product A - gets tests 1, 3, 4, and 7
Product B - gets tests 2, 3, 4, and 9
Product c - gets tests 2, 3
Product D - gets tests 2, 3, 4, and 9
Product E - gets tests 2, 3, 4, and 9
Product F - gets tests 1, 3, 4, and 7
Product G - gets tests 1, 3, 4, and 7

I want a way for Excel to "recognize" the contents of the array "1, 3, 4, 7"
and associate it with Products A, F, and G. Similarly I want Excel to
"recognize" the array "2, 3, 4, 9" and treat that in a manner such that it is
associated with Products B, D and E.

The test-groups are easily envisioned as subordinate to the Product. But, I
want to make the test-groups one rung above the Product in terms of
hierarchy; grouping Products by test-group.

There are too many variations of test arrays and too many products to do
this manually or visually in less than a day. And as with anything tedious
and manual, there is opportunity for error by doing it that way. Can Excel
handle this task with ease? I hope.

Thanks

Grouping Items by Arrays
 

I can organize the data in any way I wish. I will pull the data by Product
with the tests grouped below the product. But I can write a macro to easily
throw the data under the product out the the side of the product, or vice.
versa. So tell me how you want the data grouped and I'll group it that way.

The goal here, to reiterate, is to have excel recognize and perhaps "name" a
specific array. To continue to recognize it after assigning a "name" and to
end up with a list like the following:

Test-Group #1
Product A
Product L
Product M
Product Z
Product ZZ

Test-Group #2
Product LL
Product LP
Product MA
Product XX

Once again, I can access the Excel response in any tabular form, up-down or
side-ways, it's not a problem at this point.

With that out of the way, any suggestions as to how to "make" Excel perform
this task?

Thanks for your response...


"Toppers" wrote:

How is the data organised?

With your example:

Product column A ?
Test ID columns B to E ? e.g, 1 ,3, 4 and 7

and maximum tests per product?

And output to look like ...?

"In need of enlightenment" wrote:

I'm working with what feels like inverted hierarchy here and I need
some help in linking topics with arrays.

For the sake of illustration; I have 500 products. I run tests on each of
these products before they are approved for sale. In fact I run a group of
tests on each product. I don't run the same group-of-tests on every product,
but I do use a test-group on more than one product.

Example

Product A - gets tests 1, 3, 4, and 7
Product B - gets tests 2, 3, 4, and 9
Product c - gets tests 2, 3
Product D - gets tests 2, 3, 4, and 9
Product E - gets tests 2, 3, 4, and 9
Product F - gets tests 1, 3, 4, and 7
Product G - gets tests 1, 3, 4, and 7

I want a way for Excel to "recognize" the contents of the array "1, 3, 4, 7"
and associate it with Products A, F, and G. Similarly I want Excel to
"recognize" the array "2, 3, 4, 9" and treat that in a manner such that it is
associated with Products B, D and E.

The test-groups are easily envisioned as subordinate to the Product. But, I
want to make the test-groups one rung above the Product in terms of
hierarchy; grouping Products by test-group.

There are too many variations of test arrays and too many products to do
this manually or visually in less than a day. And as with anything tedious
and manual, there is opportunity for error by doing it that way. Can Excel
handle this task with ease? I hope.

Thanks

Grouping Items by Arrays
 

First thoughts using VBA:

A table of test groups:

A B
Test _Group #1 1,3,4,7 (or could be individual columns B to ??)
Test _Group #2 2,3,4,9
etc

Then loop(s) through the test groups/data to match test groups against the
products. Output could be as you defined it.

For 500 or so products this would be pretty quick and there is the
possibility to sort the data by test group.

HTH




"In need of enlightenment" wrote:

I can organize the data in any way I wish. I will pull the data by Product
with the tests grouped below the product. But I can write a macro to easily
throw the data under the product out the the side of the product, or vice.
versa. So tell me how you want the data grouped and I'll group it that way.

The goal here, to reiterate, is to have excel recognize and perhaps "name" a
specific array. To continue to recognize it after assigning a "name" and to
end up with a list like the following:

Test-Group #1
Product A
Product L
Product M
Product Z
Product ZZ

Test-Group #2
Product LL
Product LP
Product MA
Product XX

Once again, I can access the Excel response in any tabular form, up-down or
side-ways, it's not a problem at this point.

With that out of the way, any suggestions as to how to "make" Excel perform
this task?

Thanks for your response...


"Toppers" wrote:

How is the data organised?

With your example:

Product column A ?
Test ID columns B to E ? e.g, 1 ,3, 4 and 7

and maximum tests per product?

And output to look like ...?

"In need of enlightenment" wrote:

I'm working with what feels like inverted hierarchy here and I need
some help in linking topics with arrays.

For the sake of illustration; I have 500 products. I run tests on each of
these products before they are approved for sale. In fact I run a group of
tests on each product. I don't run the same group-of-tests on every product,
but I do use a test-group on more than one product.

Example

Product A - gets tests 1, 3, 4, and 7
Product B - gets tests 2, 3, 4, and 9
Product c - gets tests 2, 3
Product D - gets tests 2, 3, 4, and 9
Product E - gets tests 2, 3, 4, and 9
Product F - gets tests 1, 3, 4, and 7
Product G - gets tests 1, 3, 4, and 7

I want a way for Excel to "recognize" the contents of the array "1, 3, 4, 7"
and associate it with Products A, F, and G. Similarly I want Excel to
"recognize" the array "2, 3, 4, 9" and treat that in a manner such that it is
associated with Products B, D and E.

The test-groups are easily envisioned as subordinate to the Product. But, I
want to make the test-groups one rung above the Product in terms of
hierarchy; grouping Products by test-group.

There are too many variations of test arrays and too many products to do
this manually or visually in less than a day. And as with anything tedious
and manual, there is opportunity for error by doing it that way. Can Excel
handle this task with ease? I hope.

Thanks