Need data from one worksheet to be in a list in another worksheet.

[Guest]

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?

 

[Guest]

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.

 

[Biff]

Are there any duplicates in column A?

Biff

 

[Guest]

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(SMALL($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(SMALL($B:$B,ROW(A1)),$B:$B,0)))

Then select B22 and copy down as before.
Cols A and C from X will be extracted into cols C and D in the new sheet.

---

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Max

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

Kris

 

[Guest]

Delighted to hear that, Kris !
You're welcome and thanks for feeding back ..