Extracting information from Column A to Column B Automatically

[Mstg007]

I am trying to automatically extract / copy information from Column A to Column B.
But the hard part is to automatically define what rows i want extract or copy from Column A.

ColA ColB
a
b
c
d
e
f
g_line
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
h_curve
i
j
f
l
m
I am importing a file into excel. So there might be even more than 100 of the numbers I want to copy over.
From (a to g_line) will always be constant - its the header of the imported file. But from h_curve to m is consistant -
its the bottom header of the file. So 1 - 10 (or 1 - 1000) I would want to copy or extract over to Column B.
Thanks for your help!!!!

 

[Gary McMaster]

How about a "custom autofilter" on column A: Does not equal 0 (zero). Then copy/paste to column B
Another way might be to fill column "B" with a formula similar to the one shown below.
VBA code could also do the job.

Gary

Excel Workbook
	A	B
1	ColA	ColB
2	a
3	b
4	c
5	d
6	e
7	f
8	g_line
9	1	1
10	2	2
11	3	3
12	4	4
13	5	5
14	6	6
15	7	7
16	8	8
17	9	9
18	10	10
19	h_curve
20	i
21	j
22	f
23	l
24	m
Sheet1

 

[Mstg007]

thank you

 

[Mstg007]

This is what I am trying to do. The below code is the data imported into excel:

Sewers Systems
"Project Name = ",""
"SI Units? = ",#FALSE#
"Total No. Lines = ",3
"Starting HGL = ",0.00000000
"Return Period Index = ",2
"Min Cover = ",4.00000000
"Zero Min Cover at outfalls = ",#FALSE#
"Design Vel = ",3.00000000
"Min Slope = ",0.20000000
"Max Slope = ",10.00000000
"Min Pipe Size = ",12.00000000
"Max Pipe Size = ",96.00000000
"Curb Opening = ",""
LINE DATA =============================
"Network Name = ","PROPOSED STORM"
"Line No. = ",1
"Line ID = ","Pipe - (9)"
"Downstream Line No. = ",0
"X,Y Coord Dn = ",19.4154930243749,-146.2821744149114
"X,Y Coord Up = ",25.9215656661981,-31.2188056251907
"Deflection Angle = ",-86.76374854
"Inlet Time = ",5.00000000
"Line Length = ",115.24715970
"Invert Elev Dn = ",10.00000000
"Line Slope = ",0.00204952
"Invert Elev Up = ",10.23620091
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
"Line No. = ",2
"Line ID = ","Pipe - (11)"
"Downstream Line No. = ",1
"X,Y Coord Dn = ",25.9215656661981,-31.2188056251907
"X,Y Coord Up = ",83.7483229089266,-9.3996410159962
"Deflection Angle = ",66.09127805
"Inlet Time = ",5.00000000
"Line Length = ",61.80622782
"Invert Elev Dn = ",10.23600000
"Line Slope = ",0.00855612
"Invert Elev Up = ",10.76482172
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
"Line No. = ",3
"Line ID = ","Pipe - (17)"
"Downstream Line No. = ",2
"X,Y Coord Dn = ",83.7483229089266,-9.3996410159962
"X,Y Coord Up = ",155.9728320444629,-53.4800835952774
"Deflection Angle = ",52.06924185
"Inlet Time = ",5.00000000
"Line Length = ",84.61362265
"Invert Elev Dn = ",10.76500000
"Line Slope = ",0.00727805
"Invert Elev Up = ",11.38082246
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"Line Part ID = ","0A967E34-111D-4B25-AD18-52826357B9D4"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
                                                        0
"IDF Curves"
0,69.87033,0,79.25974,88.23514,102.6072,114.8193,127.1596
0,13.10001,0,14.60002,15.50002,16.50003,17.20003,17.80003
"sampleFHA.IDF"
#FALSE#,"",""
1,0,1,0
  END OF FILE
"End of file"

If I can do the above with this code that would be awesome.

 

[Gary McMaster]

You lost me with your last post.

Can you show exactly what this data looks like (using Excel Jeanie) after import but before processing and then supply a second view of what it should look like after it is processed? You can get a copy of Excel Jeanie from the very top thread "Sticky" in this forum.

Gary

 

[Mstg007]

I wish I could install that but I can not because of admin priv. If you were to copy the code to a txt file. Then open via excel. Start a new excel blank file then drag the txt into the document. It should only fill up Column A.

 

[Gary McMaster]

So the data is coming from a text file. You should try the "Import Wizard" instead of just dragging and dropping the text file into Excel. You'll have a lot more control over how it is formatted.

Google "Excel text import wizard" and have a look at the process for whatever version of Excel you are using. I used the wizard for XL2000 on the data you posted (comma delimited) and it did a pretty good job of columnizing it.

Gary

 

[Mstg007]

great!, when using the import wizard does it delete any of the contents ( commas, quotes, etc) or does it just columnize / delineates it into rows

 

[Rick Rothstein]

I wish I could install that but I can not because of admin priv. If you were to copy the code to a txt file. Then open via excel. Start a new excel blank file then drag the txt into the document. It should only fill up Column A.

Is your file a text file? If not, what is its extension?

As for your question... you told us the delimiters were "g_line" and "h_curve", but those words were no where to be found in your sample data. I am guessing the delimiters are "LINE DATA ====" and "IDF Curves", correct? Also, you said your data was numbers between them... that is not even close to correct. Anyway, maybe this formula placed in cell B2 will get you started. If not, you will need to tell us exactly what you are wanting from your data (we know you know, but you cannot expect us to know without you telling us).

=IF(AND(COUNTIF(A$1:A1,"LINE DATA*")>0,COUNTIF(A$1:A1,"*IDF Curve*")=0),MID(A2,FIND("= "",",A2&"= "",")+4,99),"")

 

[Gary McMaster]

great!, when using the import wizard does it delete any of the contents ( commas, quotes, etc) or does it just columnize / delineates it into rows

It will remove the commas and quotes.