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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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?
 

rpolasky

New Member
Joined
Mar 29, 2013
Messages
18

ADVERTISEMENT

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.
 

rpolasky

New Member
Joined
Mar 29, 2013
Messages
18
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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?
 

rpolasky

New Member
Joined
Mar 29, 2013
Messages
18
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

rpolasky

New Member
Joined
Mar 29, 2013
Messages
18
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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
Top