Variable list from array

[Robert Hatcher]

sorry for the lame subject line but I couldent come up with something
inteligent :O

I need to create a list of failed items from data in a separate sheet
based on the condition of the items as indicated in an adjacent cell.
The Data looks like:
Transducer FAILURE (Yes=1 No=0)
T7-01 1
T7-02 0
(T7-3 through T7-16 are also “0” in this case)
T7-17 1
T6-16 1
B1-05 1
B1-06 1
B6-11 1
(I skipped data here to show the variation in the numbering)
There are actual 1240 Transducers listed in the data with several
adjacent columns of information with the failure column being the only
one of concern at this point. This is someone else’s work so I cannot
change the original data layout. The target sheet is arranged by
quadrants and each quadrant has a list that contains only failed
transducers. It looks like:
T7 1 17
T6 16
T5
T4
T3
T2
T1
B1 5 6
B2
B3
B4
B5
B6 11
B7
B8
B9
B10

Each number populates a cell.

The workforce takes the database and then created this target sheet in
Excel by hand. As you can imagine it takes time and with a lot of
failed transducers, mistakes are sometimes made in the entry. The
target sheet has other information on it which locks the format up a
bit. For now I want to automate this part of the process for them. I
think an array formula on the target sheet is a way to do this.
Because this sheet will reside on a military intranet VBA is out of
the question. Any ideas or alternatives will be appreciated.
Robert

 

[Don Guillett]

sorry for the lame subject line but I couldent come up with something
inteligent :O

I need to create a list of failed items from data in a separate sheet
based on the condition of the items as indicated in an adjacent cell.
The Data looks like:
Transducer     FAILURE (Yes=1 No=0)
T7-01            1
T7-02            0
(T7-3 through T7-16 are also “0” in this case)
T7-17            1
T6-16            1
B1-05            1
B1-06            1
B6-11            1
(I skipped data here to show the variation in the numbering)
There are actual 1240 Transducers listed in the data with several
adjacent columns of information with the failure column being the only
one of concern at this point. This is someone else’s work so I cannot
change the original data layout.   The target sheet is arranged by
quadrants and each quadrant has a list that contains only failed
transducers. It looks like:
T7   1  17
T6   16
T5
T4
T3
T2
T1
B1  5   6
B2
B3
B4
B5
B6   11
B7
B8
B9
B10

Each number populates a cell.

The workforce takes the database and then created this target sheet in
Excel by hand.  As you can imagine it takes time and with a lot of
failed transducers, mistakes are sometimes made in the entry.   The
target sheet has other information on it which locks the format up a
bit.  For now I want to automate this part of the process for them.  I
think an array formula on the target sheet is a way to do this.
Because this sheet will reside on a military intranet VBA is out of
the question.  Any ideas or alternatives will be appreciated.
Robert

Is this your actual data. I am having a hard time figuring out what
you want

Send your file with a complete explanation and before/after examples
to (e-mail address removed)

 

[Robert Hatcher]

Will do Don, thanks

Is this your actual data. I am having a hard time figuring out what
you want

Send your file with a complete explanation and before/after examples
to (e-mail address removed)

 

[Pete_UK]

Hi Robert,

here's a formula-based solution. I've assumed that your data is in
Sheet1, occupying columns A and B with your headers on row 1. Put
these formulae in the cells stated:

D2: =IF(B2=0,"",LEFT(A2,SEARCH("-",A2)-1))
E2: =IF(D2="","",D2&"_"&COUNTIF(D$22,D2))
F2: =IF(E2="","",MID(A2,SEARCH("-",A2)+1,255))

You can copy these down as far as you like (even beyond your data -
you will only get blanks). This is what I get with your example data:

Transducer FAILURE (Yes=1 No=0)
T7-01 1 T7 T7_1 01
T7-02 0
T7-03 0
T7-04 0
T7-05 0
T7-06 0
T7-07 0
T7-08 0
T7-09 0
T7-10 0
T7-11 0
T7-12 0
T7-13 0
T7-14 0
T7-15 0
T7-16 0
T7-17 1 T7 T7_2 17
T6-16 1 T6 T6_1 16
B1-05 1 B1 B1_1 05
B1-06 1 B1 B1_2 06
B6-11 1 B6 B6_1 11

Column D is picking out the transducer base code, column E sets up a
sequential numbering for each code, and column F separates out the
right-hand part of the transducer code.

Then in Sheet2 I've assumed that you have a header in row1 and that
the list of transducers follows on in column A. You can use this
formula in B2:

=IF(ISNA(MATCH($A2&"_"&COLUMN(A$1),Sheet1!$E:$E,0)),"",--INDEX(Sheet1!
$F:$F,MATCH($A2&"_"&COLUMN(A$1),Sheet1!$E:$E,0)))

and this can be copied across and down as far as you need to. Here's
the results (just how you wanted them):

Transducer
T7 1 17
T6 16
T5
T4
T3
T2
T1
B1 5 6
B2
B3
B4
B5
B6 11
B7
B8
B9
B10

If you want to preserve the leading zeros, then you can just remove
the double minus in the formula, though you will then have text values
rather than numbers.

As this is formula-driven, it will respond immediately to any change
of the data on Sheet1.

Hope this helps.

Pete

 

[Robert Hatcher]

Hi Robert,

here's a formula-based solution. I've assumed that your data is in
Sheet1, occupying columns A and B with your headers on row 1. Put
these formulae in the cells stated:

D2:    =IF(B2=0,"",LEFT(A2,SEARCH("-",A2)-1))
E2:    =IF(D2="","",D2&"_"&COUNTIF(D$22,D2))
F2:    =IF(E2="","",MID(A2,SEARCH("-",A2)+1,255))

You can copy these down as far as you like (even beyond your data -
you will only get blanks). This is what I get with your example data:

Transducer   FAILURE (Yes=1 No=0)
T7-01          1            T7     T7_1    01
T7-02          0
T7-03          0
T7-04          0
T7-05          0
T7-06          0
T7-07          0
T7-08          0
T7-09          0
T7-10          0
T7-11          0
T7-12          0
T7-13          0
T7-14          0
T7-15          0
T7-16          0
T7-17          1            T7     T7_2    17
T6-16          1            T6     T6_1    16
B1-05          1            B1     B1_1    05
B1-06          1            B1     B1_2    06
B6-11          1            B6     B6_1    11

Column D is picking out the transducer base code, column E sets up a
sequential numbering for each code, and column F separates out the
right-hand part of the transducer code.

Then in Sheet2 I've assumed that you have a header in row1 and that
the list of transducers follows on in column A. You can use this
formula in B2:

=IF(ISNA(MATCH($A2&"_"&COLUMN(A$1),Sheet1!$E:$E,0)),"",--INDEX(Sheet1!
$F:$F,MATCH($A2&"_"&COLUMN(A$1),Sheet1!$E:$E,0)))

and this can be copied across and down as far as you need to. Here's
the results (just how you wanted them):

Transducer
T7          1     17
T6          16
T5
T4
T3
T2
T1
B1          5     6
B2
B3
B4
B5
B6          11
B7
B8
B9
B10

If you want to preserve the leading zeros, then you can just remove
the double minus in the formula, though you will then have text values
rather than numbers.

As this is formula-driven, it will respond immediately to any change
of the data on Sheet1.

Hope this helps.

Pete






- Show quoted text -

Thanks Pete, Im going to try this today.

 

[Robert Hatcher]

Pete, once I corrected for the variables I failed to mention, this is
working very well. One thing I did not mention is that the results
are broken into quadrants. I need to be able to test the results of
the formula :
IF(ISNA(MATCH($A1&"_"&COLUMN(B$1),'688 IP'!$E:$E,0)),"",--INDEX('688
IP'!$F:$F,MATCH($A1&"_"&COLUMN(B$1),'688 IP'!$E:$E,0)))
To see if it fits into one of the quadrants, 1-15, 16-30, 32-45 or
46-61. (31 is intentionally skipped). I was working with the OR
function but the way I came up with would require repeating your
formula 15 times for each cell. Is there a better way
For example I would have a basic formula that checks for Quadrant 1
(transducers 1-15) and use it in all the cells in that quadrant.
Thanks
Robert

 

[Pete_UK]

Robert,

I don't quite understand what you mean by Quadrants.

Apart from that, I'm due to catch a plane in about 8 hours time, so I
need to go to bed soon and I'll be off the scene for a week. In the
meantime, if you can give a few examples of what you want to achieve
and how that relates to your data then I can pick the thread up again
next week (or perhaps Don will send you back a solution).

Pete

 

[GS]

Robert Hatcher submitted this idea :

sorry for the lame subject line but I couldent come up with something
inteligent :O

I need to create a list of failed items from data in a separate sheet
based on the condition of the items as indicated in an adjacent cell.
The Data looks like:
Transducer FAILURE (Yes=1 No=0)
T7-01 1
T7-02 0
(T7-3 through T7-16 are also “0†in this case)
T7-17 1
T6-16 1
B1-05 1
B1-06 1
B6-11 1
(I skipped data here to show the variation in the numbering)
There are actual 1240 Transducers listed in the data with several
adjacent columns of information with the failure column being the only
one of concern at this point. This is someone else’s work so I cannot
change the original data layout. The target sheet is arranged by
quadrants and each quadrant has a list that contains only failed
transducers. It looks like:
T7 1 17
T6 16
T5
T4
T3
T2
T1
B1 5 6
B2
B3
B4
B5
B6 11
B7
B8
B9
B10

Each number populates a cell.

The workforce takes the database and then created this target sheet in
Excel by hand. As you can imagine it takes time and with a lot of
failed transducers, mistakes are sometimes made in the entry. The
target sheet has other information on it which locks the format up a
bit. For now I want to automate this part of the process for them. I
think an array formula on the target sheet is a way to do this.
Because this sheet will reside on a military intranet VBA is out of
the question. Any ideas or alternatives will be appreciated.
Robert

If you automate retrieval of the data from the database to begin with,
you could arrange it more efficiently how you want it displayed. I just
don't see any point in doing the first part manually just to create a
problematic condition that you now have to find an automated solution
to undo!

(Assumes you do know Excel can read a database)

 

[Robert Hatcher]

Pete, I hope you had a safe trip. What I meant was that the data is
broken up into four locations on the page corresponding to the
physical location of the transducers in the sphere that the
transducers make up. For instance Transducers 1-15 are from the
forward starboard quadrant and are represented on the upper right hand
quarter of the sheet.
In my OP I was trying to be as brief as possible but I should probably
provide more background information.

The transducers are located in a spherical array, made up of 16
vertical rows T1-T6 and B1-B10; T=Top, B=Bottom (actually more but
three rows are no longer processed). Most Rows have 80 transducers
with T6 and B6-B10 having only 60. I originally left out the
variation in transducer count per row to simplify the post knowing I
could incorporate that later. The purpose of this sheet is to provide
the site supervisor with an overhead view of the spherical array so
that he can plan out each day’s work. He uses symbols to keep track
of progress and problems as the job progresses. He creates this sheet
manually and finds it essential. I just want to eliminate the manual
step especially since his source of information is an excel workbook.
Don did email that it is “doable” and will look at it today.
Thanks again for your help.
Robert

 

[Pete_UK]

Hi Robert,

I'm back now. If you would like to send me a copy of your file to:

pashurst (at) auditel.net

(change the obvious), then I can take a closer look at it and perhaps
understand better how you want the results to look.

Hope this helps.

Pete