Excel - Fill column with data from other columns 2

[LadyLin]

I am trying to make a column from 5 other columns of data
each 50 rows long. I think I need to code a subroutine or macro but I'm not sure how to get started. Here's the set up:

Each column is filled with data or "#value!" (when there is no data). Once the #value! is encountered that's the end of the data for that column. (col a-e) Column F is the result I want.
[tt]
A B C D E F
1 data data data data #value! data from a1
2 data data #value! data #value! data from a2
3 data #value! #value! data from a3
4 #value! data from b1
5 #value! data from b2
. data from c1
. data from d1
. data from d2
50
[/tt]
Any suggestions on how to go about this would be greatly appreciated.

 

[SkipVought]

Hi,

Is this data TEXT or NUMERIC?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[LadyLin]

Text data.

 

[SkipVought]

Where is the SOURCE data that is referenced?

What is the format of the sourcce data?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[LadyLin]

The source data comes from the Spec data from the Query you helped me with the other day.

cell formulas for column a-e resemble this

=CLEAN(GetLines(CR_WO_SPECS!$C$4,A3))

 where

Public Function 
GetLines(ByVal sText As String, sNum As String) As String
  GetLines = Split(sText, Chr(13))(sNum)
End Function

 

[SkipVought]

Then what was the logic to get the source data into the current 5-column format?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[LadyLin]

Then I had a column before column A, that held the #s, 0-49
so that the formula CLEAN(GetLines(CR_WO_SPECS!$C$4,A3)) would get the correct line. In other words, if the spec field from the query, had 5 lines separated by cr,lf
the get lines would separate them into an array, using the chr(13) as the delimiter. I actually had other queries for the other columns, data all from spec fields.
To summarize
1, get data from Query
2, split each spec field into separate lines
3, fill columns a-e
4, now I want to combine into one column f
I hope I'm explaining this clearly.
Thanks for your help, I appreciate it.

 

[SkipVought]

It would sure help to SEE the source data and any intermediate results you generate that lead to columns a-e

But here's a suggestion...

Insert a row of headings on the sheet a-e

On a new sheet...

Use MS Query via Data/Get External Data/New Database Query - Excel Files -- YOUR WORKBOOK -- YOUR SHEET.

Edit the query

Select on the FIRST COLUMN

The SQL will look like...

Select F1 From YourWorkbook.YourSheet Where F1 is not null

then combine in sever UNIONS...

Select F1 From YourWorkbook.YourSheet Where F1 is not null
UNION
Select F2 From YourWorkbook.YourSheet Where F2 is not null
UNION
...

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[LadyLin]

I'll try your suggestion.
Thanks for the help so far, I may be back

 

[LadyLin]

ok, I started the union query

SELECT WO_Lines.`WO Lines`
FROM `O:\vol2\SYSPROGM\Excel Sheets\CRNew-AG03799-AVIO-V50287-4`.WO_Lines WO_Lines
WHERE (WO_Lines.`WO Lines` Is Not Null)
union
SELECT Opr_10_Lines.`Opr 10 Lines`
FROM `O:\vol2\SYSPROGM\Excel Sheets\CRNew-AG03799-AVIO-V50287-4`.Opr_10_Lines Opr_10_Lines
WHERE (Opr_10_Lines.`Opr 10 Lines` Is Not Null)

It returns the results, however they are sorted alphabetically, I want them to appear in the order entered.
Can that be done?

 

[SkipVought]

You can do an Order By clause in the query to whatever field sequences the data the way you want.

SELECT WO_Lines.`WO Lines`
FROM `O:\vol2\SYSPROGM\Excel Sheets\CRNew-AG03799-AVIO-V50287-4`.WO_Lines WO_Lines
WHERE (WO_Lines.`WO Lines` Is Not Null)
Order By WO_Lines.`Some Other Field`
union
...

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[LadyLin]

Figured it out. I added Union All and the query kept things in order. Thanks again for your help.

 

[SkipVought]

KEWEL!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}