Finding & Returning Cell data in a column

rpolasky

New Member
Joined
Mar 29, 2013
Messages
18
Hello guys & gals,

I tried using the search section and I'm not really finding what I'm looking for, so I will try to ask it here. (and if there is a topic on this same thing please redirect me)

Sheet 1 A1-A3 is where I would like to place a specific text item from Sheet 2 Column A.

In Column A on Sheet 2 There is random text throughout the column. This data changes every time I use my macro button to pull in data from a website.

What I am trying to do is find the cell that start with "TAF" & the coresponding cells below ( all the becmg texts )

I cant just do a sheet 1 a1 = sheet 2 specific cell because sometimes the "TAF" portion changes to a different row #.

Is there a way to find "TAF" in column A in sheet 2 and take all the text in that column and put it in Sheet 1 A1, and then take everything below that and put in on sheet 1 as well?

Below is a picture of "sheet 2" ... sheet 1 you can imagine is blank.

Picture1_zpsd2311689.jpg


Thanks!

RP
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hello guys & gals,

I tried using the search section and I'm not really finding what I'm looking for, so I will try to ask it here. (and if there is a topic on this same thing please redirect me)

Sheet 1 A1-A3 is where I would like to place a specific text item from Sheet 2 Column A.

In Column A on Sheet 2 There is random text throughout the column. This data changes every time I use my macro button to pull in data from a website.

What I am trying to do is find the cell that start with "TAF" & the coresponding cells below ( all the becmg texts )

I cant just do a sheet 1 a1 = sheet 2 specific cell because sometimes the "TAF" portion changes to a different row #.

Is there a way to find "TAF" in column A in sheet 2 and take all the text in that column and put it in Sheet 1 A1, and then take everything below that and put in on sheet 1 as well?

Below is a picture of "sheet 2" ... sheet 1 you can imagine is blank.

Picture1_zpsd2311689.jpg


Thanks!

RP

Hello and welcome to MrExecel.
is this going to be only one(TAF) cell per column
 
Upvote 0
Sheet1, A1, just enter:
Rich (BB code):
=MATCH("TAF*",Sheet2!A:A,0)

Sheet1, A2, just enter:
Rich (BB code):
=MATCH(REPT("z",255),Sheet2!A:A)-A1

Sheet1, A3, just enter, and copy down:
Rich (BB code):
=IF(ROWS($A$3:A3)<=$A$2,INDEX(Sheet2!A:A,ROWS($A$4:A4)+$A$1),"")

Is this what you wanted to have?
 
Upvote 0
Sheet1, A1, just enter:
Rich (BB code):
=MATCH("TAF*",Sheet2!A:A,0)

Sheet1, A2, just enter:
Rich (BB code):
=MATCH(REPT("z",255),Sheet2!A:A)-A1

Sheet1, A3, just enter, and copy down:
Rich (BB code):
=IF(ROWS($A$3:A3)<=$A$2,INDEX(Sheet2!A:A,ROWS($A$4:A4)+$A$1),"")

Is this what you wanted to have?

Let me try it.
 
Upvote 0
Sheet1, A1, just enter:
Rich (BB code):
=MATCH("TAF*",Sheet2!A:A,0)

Sheet1, A2, just enter:
Rich (BB code):
=MATCH(REPT("z",255),Sheet2!A:A)-A1

Sheet1, A3, just enter, and copy down:
Rich (BB code):
=IF(ROWS($A$3:A3)<=$A$2,INDEX(Sheet2!A:A,ROWS($A$4:A4)+$A$1),"")

Is this what you wanted to have?

No sir,

The formulas above are giving me numbers not the text that is in the cell.
 
Upvote 0
No sir,

The formulas above are giving me numbers not the text that is in the cell.

Sheet2... (where the data is)
Station
City
Metar
Metar
Metar
TAF KMKK 291659z
bcmg 2921/2923 vrb05kt
bcmg 3003/3009 vrb05kt

<colgroup><col style="width: 116pt; mso-width-source: userset; mso-width-alt: 5489;" width="154"> <tbody>
</tbody>

Sheet1... (result)
bcmg 2921/2923 vrb05kt
bcmg 3003/3009 vrb05kt

<colgroup><col style="width: 116pt; mso-width-source: userset; mso-width-alt: 5489;" width="154"> <tbody>
</tbody>

Is this expected result?
 
Upvote 0
Yes! with the addition of the line that has "TAF" in it being the the first one, then all the ones below that "TAF" line to follow.
 
Upvote 0
Yes! with the addition of the line that has "TAF" in it being the the first one, then all the ones below that "TAF" line to follow.

The set up I posted did that while excluding the TAF line... What follows is modified to include that too...

Sheet2, column A...
10
3
TAF KMKK 291659z
bcmg 2921/2923 vrb05kt
bcmg 3003/3009 vrb05kt

<colgroup><col style="width: 147pt; mso-width-source: userset; mso-width-alt: 6968;" width="196"> <tbody>
</tbody>

A1, just enter:

=MATCH("TAF*",Sheet2!A:A,0)

A2, just enter:

=MATCH(REPT("z",255),Sheet2!A:A) -A1+1

A3, just enter and copy down:

=IF(ROWS($A$3:A3)<=$A$2,INDEX(Sheet2!A:A,ROWS($A$4:A4)-1+$A$1),"")

A1 and A2 are needed to generate the list with TAF and the values which follow.
 
Upvote 0
I put it in there just as you said, and it returned the lines above the "taf" line ( METAR, etc ) I need the TAF line itself and below.

I think it has to do with something in the INDEX portion of the formula, because it did work as intended... just Up not Down.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top