[Solved] How to flush left several columns of data?

Discuss the spreadsheet application

[Solved] How to flush left several columns of data?

Postby jonhaloi21 » Sat Nov 09, 2019 12:43 am

How do i do this?
Attachments
flush left.jpg
Last edited by Hagar Delest on Sun Nov 10, 2019 12:40 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby RusselB » Sat Nov 09, 2019 1:14 am

Welcome to the Forums.
If this is going to be a one time situation, I'd suggest just doing it manually.
If this is something that you're going to need done on a regular basis, do you want the results in the same columns as what you started with or in a different set of columns?
Is there a limit as to the number of columns that will have to be dealt with? If so, how many?
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to flush left several columns of data?

Postby MrProgrammer » Sat Nov 09, 2019 2:08 am

Hi, and welcome to the forum.

jonhaloi21 wrote:How do I [left-align the cells in a row]?
• Use [Tutorial] Sorting and Filtering data with formulas where the sort criteria (ascending) is that the cell is empty, or
• Read [Solved] Merge/allign cells in Columns macro, or
• Save a copy as CSV, remove leading/consecutive commas with Writer, copy the text, and use Paste → Special → Unformatted text in Calc, triggering the Text Import dialog

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3953
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sat Nov 09, 2019 5:23 am

Russell, thanks for the questions. There are a total of 14 columns ("B" through "O") which I do every day for 3 sets of data, so It would be great if I could do this in an automated way, instead of manually.
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sat Nov 09, 2019 6:13 am

MrProgrammer, thanks for the reply. I don't know how to run macros, or use Python. I am not a programmer. I know that excel allows the user to do this operation in a few steps. I tried the save as CSV method you mention. It pastes all the date into one cell. For my needs, the data should be in unique cells and flush left as there are other things I need to do with the data.
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby robleyd » Sat Nov 09, 2019 6:39 am

Did you select a Field Delimiter when you exported the data?
oo_csv_export.png

Exporting the data as shown in the screen capture above produces this:
Code: Select all   Expand viewCollapse view
Jan,1
Feb,2
Mar,3
Apr,4
May,5
Jun,6
Jul,7
Aug,8
Sep,9
Oct,10
Nov,11
Dec,12

As a matter of interest, what is the problem you are trying to resolve by shifting your data around like this - there may be an alternative solution to your overall problem.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3145
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sat Nov 09, 2019 7:43 am

Yes, the delimiters you mentioned were the same in my case. I need to produce "market profiles" of futures trading contracts like S&P500, Nasdaq and the Russell2000 futures. My original post has a market profile on the right side of the chart. A market profile is basically a distribution of time-price opportunities during the trading day. "B" period =9:30-10:00am, "C" period =10:00-10:30" etc. The price range for each time period is recorded and flushed left. The shape of the profile can give entry / exit clues to trading. There is an alternative solution, but it costs US$150 a month! Currently, I do this manually in OOcalc.
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby robleyd » Sat Nov 09, 2019 8:02 am

If you receive this data as CSV and import it and it gives you empty cells, one possibility might be to edit the data before import into Calc, so it is laid out as you want. Unless of course there are other steps that haven't been mentioned??
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3145
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sat Nov 09, 2019 3:27 pm

Thanks robleyd, I guess the question is how do I separate data so that it places each letter in its own cell? Is there some character other than a comma that will produce this effect?
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby Zizi64 » Sat Nov 09, 2019 6:00 pm

I don't know how to run macros, or use Python. I am not a programmer. I know that excel allows the user to do this operation in a few steps.



Use the LibreOffice.

Select the yellow range in the LibreOffice Calc, together with the first helper cell in my sample file – Then Ctrl-C.

Then select the green cell. Edit - Paste Special - Paste Unformatted Text (Options: TAB separator; Merge delimiters) - OK.
Done!

Sample_Zizi64.png


Strange ordering.ods
(118.92 KiB) Downloaded 6 times
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8772
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to flush left several columns of data?

Postby Bill » Sat Nov 09, 2019 6:39 pm

jonhaloi21 wrote:...I tried the save as CSV method you mention. It pastes all the date into one cell.

This suggests that you didn't select the proper delimiters on the Text Import dialog when you reopened the CSV file. If you use commas as delimiters when saving the file and don't select commas as delimiters on the Text Import dialog when you reopen the file, then the commas will be treated as data, not as delimiters. In addition to selecting the correct delimiter, you also must select the "Merge delimiters" option on the Text Import dialog. This worked for me.
AOO 4.1.7 and LO 6.3.2.2 on Manjaro KDE
Bill
Volunteer
 
Posts: 7581
Joined: Sat Nov 24, 2007 6:48 am

Re: How to flush left several columns of data?

Postby F3K Total » Sat Nov 09, 2019 6:59 pm

Zizi64 wrote:Use the LibreOffice.
Great idea!

If not possible to use LibreOffice, here's a small code to do the job on the selected cells in ApacheOpenOffice.

Code: Select all   Expand viewCollapse view
Sub Sort_Cellrange
    osel = thisComponent.currentselection
    if osel.supportsservice("com.sun.star.sheet.SheetCellRange") then
        oRA = oSel.Rangeaddress
        for j = oRA.Endcolumn to oRA.Startcolumn + 1 step - 1
            for i = oRA.Endcolumn to oRA.Startcolumn + 1 step - 1
                 for k = oRA.StartRow to oRA.Endrow
                     ocell = oSel.getcellbyposition(i,k)
                     oCellleft = oSel.getcellbyposition(i-1,k)
                     if ocell.formula <> "" And oCellleft.formula = "" then
                         oCellleft.formula = ocell.formula
                         oCell.formula = ""
                     endif
                 next k
            next i
        next j
    else
        msgbox ("please select a cellrange",16,"Error")
    endif
End Sub
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: How to flush left several columns of data?

Postby Lupp » Sat Nov 09, 2019 11:18 pm

Generally I prefer to do any reorganization of data without changing the original import/input.
To do so spreadsheets should provide means to create the reorganized sets based on formulas.
A thoroughly planned set of functions should have the power to solve lots of problems in a larger range by a kind of their cooperation organized as formulas.

If standard functions do not provide the needed functionality, it may be a good idea to create user functions on the same conceptual basis of manifold usability instead of solving every other day a new problem with specialized code.

If somebody is interested in an exemplification of the above statements, a glance into the attached document might help. It was primarily made with LibreOffice, but it also contains a user function being able to roughly replace the TEXTJOIN() (since LibO V5.4.4) not available in AOO. As TEXTJOIN() itself it is lacking some obviously useful capabilities, in specific the suppression of repetitions. Not too difficult to implement ... Sorting on the fly would also be useful.
Attachments
aoo99966CombineTextjoinTextsplit_0_notyetposted.ods
(18.02 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2669
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sun Nov 10, 2019 3:33 am

F3K tool Thanks, I'll give it a try, though I don't know how to put that code into OO.
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am

Re: How to flush left several columns of data?

Postby jonhaloi21 » Sun Nov 10, 2019 3:56 am

Bill-you are absolutely correct. I had not selected the correct delimiters, when I did tried again, it worked!! Thanks so much!!!
OpenOffice 4.1.7 on MacOS 10.10.5
jonhaloi21
 
Posts: 7
Joined: Sat Nov 09, 2019 12:32 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests