Row Re-alignment

After searching these groups, I don't think I've found a solution to
the following issue: When data is output in excel format from the
source application, it shifts all rows to the left rather than insert a
space for a null result. The result looks something like this:

Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3

The top row represents the header. The additional rows represent the
unsorted data. I would like to find an elegant solution that places the
correct cells under the correct columns like this:

Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3


We currently perform this sort in Access, but it's a six-step process
for each column!

Thanks,
JMark

Unfortunately, my post above didn't sort the "correct columns" as
intended. All Col. 1 should be in one column, all Data 1 should be in
the next, etc.

Thanks

Hi

What type of data you get from source application? A text file? When yes,
then maybe you can replace all cpaces with commas or semicolons (depends on
what your regional settings determine as delimiter), and save the output as
csv-file (directly from your source app, as it is possible, or through some
VBA procedure).

I.e. in notepad you see the file YourFile.csv like this:
Data1;Data2;Data3
Data1;Data2;Data3
;;Data2;Data3
;;;Data3
Data1;Data2;Data3
;;Data2;Data3
;;;Data3

Now, if in Excel you select File>Open, set file type as *.csv, and open
YourFile.csv - you see:
Data1 Data2 Data3
Data1 Data2 Data3
Data2 Data3
Data3
Data1 Data2 Data3
Data2 Data3
Data3

You can copy data from there, and paste into an existing Excel file, or you
can save opened csv-file as an Excel file.


Arvi Laanemets


<JMark0957@gmail.com> wrote in message
news:1145637392.317203.182980@e56g2000cwe.googlegroups.com...
> After searching these groups, I don't think I've found a solution to
> the following issue: When data is output in excel format from the
> source application, it shifts all rows to the left rather than insert a
> space for a null result. The result looks something like this:
>
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 2 Data 2 Col. 3 Data 3
> Col. 3 Data 3
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 2 Data 2 Col. 3 Data 3
> Col. 3 Data 3
>
> The top row represents the header. The additional rows represent the
> unsorted data. I would like to find an elegant solution that places the
> correct cells under the correct columns like this:
>
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 2 Data 2 Col. 3 Data 3
> Col. 3 Data 3
> Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
> Col. 2 Data 2 Col. 3 Data 3
> Col. 3 Data 3
>
>
> We currently perform this sort in Access, but it's a six-step process
> for each column!
>
> Thanks,
> JMark
>

Excellent question, Arvi. The app outpurs the data in dbf format with
no other options. I tried several "save as" options after opening it in
E2003 to no avail.

Hi

Strange. DBF-tables are real database tables, i.e. data are bound to column,
and it's placement is really irrelevant.

How are you exporting those data. You can open any DBF table from Excel
directly - simply change the file format in Open File dialog window. From
there you can copy data into Excel file, or you can save it as an excel
file.

When data in DBF-table opened with Excel are messed up anyway, then it means
they are messed up in DBF-table too, and your app is corrupted or faulty.
When this is the case, try to open them with FoxPro (when it is possible) -
probably it will have problems too.


Arvi Laanemets


"JMark" <JMark0957@gmail.com> wrote in message
news:1145646032.910824.11110@i40g2000cwc.googlegroups.com...
> Excellent question, Arvi. The app outpurs the data in dbf format with
> no other options. I tried several "save as" options after opening it in
> E2003 to no avail.
>

It is quite likely an output issue. However since I can't change it, I
must find a solution to sort the rows into the correct columns. I did
try opening the mdf file in Access, but the irregular alignment was the
same.

Thanks

How do you know which data is missing? If you have two items of data in
one record, then your assumption seems to be that it would be the first
field which should be blank, but why not Data1-blank-Data3? Are the
data items different? e.g. dates, text, numbers?

Pete

After the first 5 posts I am becoming confused about this.

The original question (shown below) stated 'data is output in excel format from the source application,' but does not specify which 'source app',. Data is then displayed which, unfortunately does not column align, and the OP posted the explanation "my post above didn't sort the "correct columns" as intended. All Col. 1 should be in one column, all Data 1 should be in the next, etc"
Can I assume that was meaning the data 'as required' (ie the second batch posted) and NOT the data supplied (the first batch posted), should be considered column aligned?

When (quote) "data is output in excel format" (endq) one would expect the leading blank cells would be output, OR, that data should be preceeded by an identifier followed by the data. (ie Data 3:data.in.cell.3.etc). The comment 'likely an output issue. However since I can't change it, I
must find a solution' suggests a solution is needed.

If data were supplied as shown in the original list, and there were (say) a maximum 4 columns possible, then the following formula in AA1, AA2, AA3 and AA4 (formula copy down as far as etc) would extract into the correct coumns.

in AA1
=IF($A1="Data 1",$B1,"")

in AB1
=IF($A1="Data 2",$B1,IF($C1="Data 2",$D1,""))

in AC1
=IF($A1="Data 3",$B1,IF($C1="Data 3",$D1,IF($E1="Data 3",$F1,"")))

in AD1
=IF($A1="Data 4",$B1,IF($C1="Data 4",$D1,IF($E1="Data 4",$F1,IF($G1="Data 4",$H1,""))))


If this organises your data correctly, then select columns AA to AD, copy, and Paste Special = Values back over themselves.

If this doesn't solve your problem, can you post a small sample of your data and the format you would like to see it.

Cheers

--

Originally Posted by JMark0957@gmail.com

After searching these groups, I don't think I've found a solution to
the following issue: When data is output in excel format from the
source application, it shifts all rows to the left rather than insert a
space for a null result. The result looks something like this:

Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3

The top row represents the header. The additional rows represent the
unsorted data. I would like to find an elegant solution that places the
correct cells under the correct columns like this:

Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3
Col. 1 Data 1 Col. 2 Data 2 Col. 3 Data 3
Col. 2 Data 2 Col. 3 Data 3
Col. 3 Data 3


We currently perform this sort in Access, but it's a six-step process
for each column!

Thanks,
JMark

Thanks for your help, Bryan and apologies for any confusion. Hopefully
this will help:

1. Source App = ADP eTime (Employee hours worked data)
2. Export format = *.dbf (dBase 4) (Opened in Excel), no other export
options available
3. Data size = 20 columns in "CATEG_1", "TOTAL_1", "CATEG_2",
"TOTAL_2", etc to "CATEG_10", TOTAL_10"; 5,000+ rows sorted by employee
ID, date, etc.
4. See my original posting for a data output sample (eight of 20
potential columns.) I cannot post an accurate depiction of the column
sort due to Google groups tab inconsistencies.

5. Issue = All data is output in flush left, not according to header. I
need to sort all 10 components of paid time: Billable, Non-Billable,
Per. Time, OVT, GrandTot, etc. under the correct headers.

Desired output (four of eight possible columns):

A B C D
RegTot PerTot BillTot GrandTot
540 540 540
555 555 555
525 525 525
120 360 120 480
120 360 120 480

NOTE: If this post doesn't sort correctly, I'll send an excel sample
via email.

Thanks again,
JMark
jmark0957 "at" gmail.com

email with request for sample sent

as .dbf format there are probably tab separators which would suffice.

for the simple 4 column entry shown, one would allocate the right-hand figure to the Grandtot, the next figure to the Billtot, the first figure to Regtot, and if four figures are supplied, the second to Pertot, but 20 columns will be more difficult. A sample input file (.dbf) will be usefull

--

Originally Posted by JMark

Thanks for your help, Bryan and apologies for any confusion. Hopefully
this will help:

1. Source App = ADP eTime (Employee hours worked data)
2. Export format = *.dbf (dBase 4) (Opened in Excel), no other export
options available
3. Data size = 20 columns in "CATEG_1", "TOTAL_1", "CATEG_2",
"TOTAL_2", etc to "CATEG_10", TOTAL_10"; 5,000+ rows sorted by employee
ID, date, etc.
4. See my original posting for a data output sample (eight of 20
potential columns.) I cannot post an accurate depiction of the column
sort due to Google groups tab inconsistencies.

5. Issue = All data is output in flush left, not according to header. I
need to sort all 10 components of paid time: Billable, Non-Billable,
Per. Time, OVT, GrandTot, etc. under the correct headers.

Desired output (four of eight possible columns):

A B C D
RegTot PerTot BillTot GrandTot
540 540 540
555 555 555
525 525 525
120 360 120 480
120 360 120 480

NOTE: If this post doesn't sort correctly, I'll send an excel sample
via email.

Thanks again,
JMark
jmark0957 "at" gmail.com

"Props" to Bryan for his elegant macro. This resolves my issue.

Thanks Bryan.

JMark