Formula

[Guest]

On sheet X
Cell A1 either displays an "S" or an "R". ( this comes in when the numbers
in B1 thru B16 is downloaded.
On sheet X
Cells B1 thru B16 will display from 6 to 16 numbers in any random order
ex. 3-4-7-1-9-5-16-2, , , .
Note: They are never the same or is there ever a duplication of a single
number in the same set of 6 to 16. The data set comes in ten times a day 9
different sets at a time. This part of the display is complete.

This is what I need, , ,
On sheet Z
Cell Q1 needs to display
If A1 = S then display the first four numbers in B1:B16 ex. 1,2,3,4 (
this is an easy IF statement but the next one if not an S but is an R has me
wondering )

If A1 = R then display the FIRST THREE AND THE LAST THREE NUMBERS in B1:B16
ex. 1,2,3 / 4,5,6 or 1,2,3 / 14,15,16 depending on length of number values

Can anyone supply me with a formula for cell Q1 on sheet Z to do this? Thanks

 

[Guest]

you can use a nested IF

=IF(A1="S", LEFT(B1, 4), IF(A1="R", LEFT(B1, 3) & RIGHT(B1, 3), "A1 is
neither an S nor an R"))

 

[Guest]

Verge,
I think what he wants to do is to concatenate the various numbers in the
cells B1 through B16 when the value in A1 is either S or R, rather than
working on first/last cells in any one of those cells.

What I've come up with is below. It depends on a couple of things to work
properly:
That there always be at least 3 cells in B1:B16 used, and that there are not
any empty cells in those lists until the last entry is encountered. By that
I mean that you can't have 6 numbers, but have them in B1:B3 and B5:B7, with
B6 empty.

It looks ugly, but seems to work:
=IF(X!A1="S",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " |
"),IF(X!A1="R",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " |
",OFFSET(X!B1,COUNT(X!B$1:B$16)-3,0)," | ",
OFFSET(X!B1,COUNT(X!B$1:B$16)-2,0), " | ",
OFFSET(X!B1,COUNT(X!B$1:B$16)-1,0)),"Neither S nor R"))

I used the | symbol as a separator to try to help keep the confusion down
about where the commas are that separate the CONCATENATE parameters and the
character to be used to separate the individual cell results that are being
concatenated.

Hopefully one of us is right in our attempt to help Shu.

 

[Guest]

OOPS!! My "COUNT" should have been COUNTA in all instances, so this works
just a little better <g>

=IF(X!A1="S",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " |
"),IF(X!A1="R",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " |
",OFFSET(X!B1,COUNTA(X!B$1:B$16)-3,0)," | ",
OFFSET(X!B1,COUNTA(X!B$1:B$16)-2,0), " | ",
OFFSET(X!B1,COUNTA(X!B$1:B$16)-1,0)),"Neither S nor R"))