Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

You will need to accomplish this with code. Programmatically, you can have
your macro cycle through the entire sheet & when your condition is met, it
will copy over the contents you desire.
--
http://HelpExcel.com

516-984-0252


"Kris R" wrote:

Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Are there any duplicates in column A?

Biff

"Kris R" <Kris wrote in message
...
Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col
A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1
because
I will need to change it often, but in worksheet 1 that number has an
hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Here's a non-array formulas play which could deliver the desired results ..

Assume source data in cols A to D in sheet named: X, data from row2 down

12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
etc

In a new sheet,
A1 will house the input, eg: 12956

Put in B2:
=IF($A$1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$1,X!A2)))0,ROW(),""))
(Leave B1 empty)

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!A:A,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))
Copy C2 across to F2

Then just select B2:F2 and copy down to cover the max expected extent of
data in X, say down to F100. Hide away col B. Cols C to F will return the
results from X, with all lines neatly bunched the top.

If you only want to extract cols A and C from X into the new sheet,
then skip the "Copy C2 across to F2" step above.

Instead, just put in D2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!C:C,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))

Then select B2:D2 and copy down as before.
Cols A and C from X will be extracted into cols C and D in the new sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kris R" wrote:

Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Hi Biff,

No, there are no duplicates in column A. There should never be for example
two 123456-01, instead one would be -01 and another may be -01B

Kris

"Biff" wrote:

Are there any duplicates in column A?

Biff

"Kris R" <Kris wrote in message
...
Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col
A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1
because
I will need to change it often, but in worksheet 1 that number has an
hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Max,

Thanks for the help. I will try this and see how it works. I was looking at
a lot of the functions below, but I didn't know how to string them all
together.

Kris

"Max" wrote:

Here's a non-array formulas play which could deliver the desired results ..

Assume source data in cols A to D in sheet named: X, data from row2 down

12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
etc

In a new sheet,
A1 will house the input, eg: 12956

Put in B2:
=IF($A$1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$1,X!A2)))0,ROW(),""))
(Leave B1 empty)

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!A:A,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))
Copy C2 across to F2

Then just select B2:F2 and copy down to cover the max expected extent of
data in X, say down to F100. Hide away col B. Cols C to F will return the
results from X, with all lines neatly bunched the top.

If you only want to extract cols A and C from X into the new sheet,
then skip the "Copy C2 across to F2" step above.

Instead, just put in D2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!C:C,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))

Then select B2:D2 and copy down as before.
Cols A and C from X will be extracted into cols C and D in the new sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kris R" wrote:

Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Ed,

Thanks for your response. It's been about 5 years since I've written code
and frankly, I only remember enough to know that I don't want to even attempt
it. :)

Kris

"galimi" wrote:

You will need to accomplish this with code. Programmatically, you can have
your macro cycle through the entire sheet & when your condition is met, it
will copy over the contents you desire.
--
http://HelpExcel.com

516-984-0252


"Kris R" wrote:

Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Hi Max

Your formula worked PERFECTLY!! Thank you so very much.

Kris

"Max" wrote:

Here's a non-array formulas play which could deliver the desired results ..

Assume source data in cols A to D in sheet named: X, data from row2 down

12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
etc

In a new sheet,
A1 will house the input, eg: 12956

Put in B2:
=IF($A$1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$1,X!A2)))0,ROW(),""))
(Leave B1 empty)

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!A:A,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))
Copy C2 across to F2

Then just select B2:F2 and copy down to cover the max expected extent of
data in X, say down to F100. Hide away col B. Cols C to F will return the
results from X, with all lines neatly bunched the top.

If you only want to extract cols A and C from X into the new sheet,
then skip the "Copy C2 across to F2" step above.

Instead, just put in D2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!C:C,MATCH(SMAL L($B:$B,ROW(A1)),$B:$B,0)))

Then select B2:D2 and copy down as before.
Cols A and C from X will be extracted into cols C and D in the new sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kris R" wrote:

Worksheet 1
A B C D
12956-01 A 5 9
94539-03B D 3 7
45567-02 A 20 8
12956-02B B 12 2
947345-05 Q 2 3

Worksheet 2
A B C
12956
12956-01 5
12956-02B 12

Is there a formula where I can take everything starting with 12956 in Col A
and its value in Col C from Worksheet 1 and put it in Worksheet 2? In
worksheet 2 I have the 6 digit number I am looking for located in A1 because
I will need to change it often, but in worksheet 1 that number has an hyphen
with up 3 characters behind it.

My worksheet 1 has a few thousand rows and I just can't seem to find the
right formula. Can anyone help?

Delighted to hear that, Kris !
You're welcome and thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kris R" wrote:
Hi Max
Your formula worked PERFECTLY!! Thank you so very much.
Kris