Help Please!

I have a list of 30,000 alpha-numeric numbersin column "A". I would like to
write a formula that would extract the info in A1 and A10, and repeat that
every 10 numbers.
In this example, I would want B1 to return D5F3M1-D5F3N1, The first number
and the last number in that range. Then C1 would return D5F3N2-D5F3P2, and
so on.

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet A4 & A5

Example to follow.
D5F3M1
D5F3M2
D5F3M3
D5F3M4
D5F3M5
D5F3M6
D5F3M7
D5F3M8
D5F3M9
D5F3N1

D5F3N2
D5F3N3
D5F3N4
D5F3N5
D5F3N6
D5F3N7
D5F3N8
D5F3N9
D5F3P1
D5F3P2


Sincerely,

Tom Perry

Assuming your data starts in A1 and goes down to A30000, put this
formula in B1:

=INDEX($A$1:$A$30000,COLUMNS($B1:B1)*10-9)&"-"&INDEX($A$1:$A
$30000,COLUMNS($B1:B1)*10)

(all one formula - be wary of line-breaks, which sometimes inserts
unwanted hyphens). Then copy this formula across into C1 up to
whatever. Unless you are using Excel 2007, you will probably run out
of columns before you get to the end of your list. If you want to put
the ranges in B1 downwards, use this variation:

=INDEX($A$1:$A$30000,ROW()*10-9)&"-"&INDEX($A$1:$A$30000,ROW()*10)

I don't understand your final question, so I can't help you with that.

Hope this helps.

Pete


On Jun 6, 10:00 pm, talltom wrote:
Help Please!

I have a list of 30,000 alpha-numeric numbersin column "A". I would like to
write a formula that would extract the info in A1 and A10, and repeat that
every 10 numbers.
In this example, I would want B1 to return D5F3M1-D5F3N1, The first number
and the last number in that range. Then C1 would return D5F3N2-D5F3P2, and
so on.

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet A4 & A5

Example to follow.
D5F3M1
D5F3M2
D5F3M3
D5F3M4
D5F3M5
D5F3M6
D5F3M7
D5F3M8
D5F3M9
D5F3N1

D5F3N2
D5F3N3
D5F3N4
D5F3N5
D5F3N6
D5F3N7
D5F3N8
D5F3N9
D5F3P1
D5F3P2

Sincerely,

Tom Perry

Pere_UK

Thank you very much, these formulae work better than slicing melted butter.
:)

About my second question;

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet A4 & A5

Sheet1! and part of Sheet2!
On my list of 30K, I first copied and pasted 200 #'s in B1, and in C1 wrote
the formula =SUM(B1), then in C2 I wrote =SUM(B200), and repeated this 150
times.

Then on Sheet3! so I could print all 150 pairs of answers on one sheet to
easily reference.

I wrote starting in B1; I wrote Down 13 sets of 3 rows, then over to the
right starting in D1, D2, etc. 12 times to get all 150 pairs of answers.
Column A1
1 =SUM(Sheet1!B1) and
=SUM(Sheet1!B2)
Column A4 Blank Row
2 =SUM(Sheet1!D1) and
=SUM(Sheet1!D2)
Etc.

I was looking for an easier way to get the info from Sheet1! and Sheet2!
onto my summary sheet; Sheet3!

This took me about 2 hours. I think there is an easier way to do this.

Sorry for being so wordy.

Tom Perry



"Pete_UK" wrote:

Assuming your data starts in A1 and goes down to A30000, put this
formula in B1:

=INDEX($A$1:$A$30000,COLUMNS($B1:B1)*10-9)&"-"&INDEX($A$1:$A
$30000,COLUMNS($B1:B1)*10)

(all one formula - be wary of line-breaks, which sometimes inserts
unwanted hyphens). Then copy this formula across into C1 up to
whatever. Unless you are using Excel 2007, you will probably run out
of columns before you get to the end of your list. If you want to put
the ranges in B1 downwards, use this variation:

=INDEX($A$1:$A$30000,ROW()*10-9)&"-"&INDEX($A$1:$A$30000,ROW()*10)

I don't understand your final question, so I can't help you with that.

Hope this helps.

Pete


On Jun 6, 10:00 pm, talltom wrote:
Help Please!

I have a list of 30,000 alpha-numeric numbersin column "A". I would like to
write a formula that would extract the info in A1 and A10, and repeat that
every 10 numbers.
In this example, I would want B1 to return D5F3M1-D5F3N1, The first number
and the last number in that range. Then C1 would return D5F3N2-D5F3P2, and
so on.

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet A4 & A5

Example to follow.
D5F3M1
D5F3M2
D5F3M3
D5F3M4
D5F3M5
D5F3M6
D5F3M7
D5F3M8
D5F3M9
D5F3N1

D5F3N2
D5F3N3
D5F3N4
D5F3N5
D5F3N6
D5F3N7
D5F3N8
D5F3N9
D5F3P1
D5F3P2

Sincerely,

Tom Perry

Sat, 9 Jun 2007 16:46:00 -0700 from talltom
:
Thank you very much, these formulae work better than slicing melted butter.

I should hope so! Slicing melted butter would be about as effective
as moving water with a fork. :-)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Pete_UK

The INDEX formulae worked great!!! Thank You very Much!!

About my second question;

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet 2 A4 & A5, with a blank row
between A2 & A4

Sheet1! and part of Sheet2!
On my list of 30K, I first copied and pasted 200 #'s in B1, and in C1 wrote
the formula =SUM(B1), then in C2 I wrote =SUM(B200), and repeated this 150
times.

Then on Sheet3! so I could print all 150 pairs of answers on one sheet to
easily reference.

I wrote starting in B1; I wrote Down 13 sets of 3 rows, then over to the
right starting in D1, D2, etc. Repeated 12 times to get all 150 pairs of
answers.

Column A1
1 =SUM(Sheet1!B1) and
=SUM(Sheet1!B2)
Column A4 Blank Row
2 =SUM(Sheet1!D1) and
=SUM(Sheet1!D2)
Etc.

I was looking for an easier way to get the info from Sheet1! and Sheet2!
onto my summary sheet; Sheet3!

This took me about 2 hours. I hope there is an easier way to do this.

Sorry for being so wordy.



"Pete_UK" wrote:

Assuming your data starts in A1 and goes down to A30000, put this
formula in B1:

=INDEX($A$1:$A$30000,COLUMNS($B1:B1)*10-9)&"-"&INDEX($A$1:$A
$30000,COLUMNS($B1:B1)*10)

(all one formula - be wary of line-breaks, which sometimes inserts
unwanted hyphens). Then copy this formula across into C1 up to
whatever. Unless you are using Excel 2007, you will probably run out
of columns before you get to the end of your list. If you want to put
the ranges in B1 downwards, use this variation:

=INDEX($A$1:$A$30000,ROW()*10-9)&"-"&INDEX($A$1:$A$30000,ROW()*10)

I don't understand your final question, so I can't help you with that.

Hope this helps.

Pete


On Jun 6, 10:00 pm, talltom wrote:
Help Please!

I have a list of 30,000 alpha-numeric numbersin column "A". I would like to
write a formula that would extract the info in A1 and A10, and repeat that
every 10 numbers.
In this example, I would want B1 to return D5F3M1-D5F3N1, The first number
and the last number in that range. Then C1 would return D5F3N2-D5F3P2, and
so on.

The last thing I would like to ask is how to write a formula to summarize
this info on a separate sheet. Example: Info on Sheet 1 in B1 & B2, D1 &
D2, to display on Sheet 2 A1 & A2, and Sheet A4 & A5

Example to follow.
D5F3M1
D5F3M2
D5F3M3
D5F3M4
D5F3M5
D5F3M6
D5F3M7
D5F3M8
D5F3M9
D5F3N1

D5F3N2
D5F3N3
D5F3N4
D5F3N5
D5F3N6
D5F3N7
D5F3N8
D5F3N9
D5F3P1
D5F3P2

Sincerely,

Tom Perry