Page 1 of 1

[Solved] Converting Windows Form Data

Posted: Sun Feb 28, 2016 2:55 pm
by lmstearn
Hi there! Have some ancient looking Windows form data that we wish to paste into the spreadsheet in order to create templates for common screen resolutions 720p, 1080p, 2160p like so:

Code: Select all

LTEXT           "Add",IDC_STATIC,506,10,14,8
EDITTEXT        IDC_TEXT,528,7,120,14,ES_AUTOHSCROLL
EDITTEXT        IDC_NUMBER,647,7,21,14,ES_NUMBER
LTEXT           "times.",IDC_STATIC,671,10,23,8
LISTBOX         IDC_LIST,7,22,641,148,LBS_NOINTEGRALHEIGHT | LBS_EXTENDEDSEL | WS_VSCROLL | WS_TABSTOP //principal, main, or chief control on form
PUSHBUTTON      "&Add",IDC_ADD,650,30,46,14
PUSHBUTTON      "&Up",IDC_UP,650,47,47,14
PUSHBUTTON      "&Down",IDC_DOWN,650,63,47,14
PUSHBUTTON      "&Sideways",IDC_CREATE,650,80,47,14
PUSHBUTTON      "&UpsideDown",IDC_REMOVE,650,97,47,14
PUSHBUTTON      "&Less",IDC_CLEAR,650,114,47,14
PUSHBUTTON      "&More",IDC_LOGON,650,131,47,14
PUSHBUTTON      "&NoMore",IDC_NOLOGON,650,148,47,14
LTEXT           "Great",IDC_STATIC_ONE,530,180,70,8
CTEXT           "-",IDC_SHOWCOUNT,600,180,25,8
LTEXT           "Fantastic",IDC_STATIC_TWO,625,180,30,8 

The aim is to copy the conversion formulas to a range of cells and copy this data in. (don't care about the non-numerical stuff) The data comprises of both x and y values so we'll need to perform the operation in two passes:
For example convert to 1080p treat all as x values and multiply by 1920/1368. Then undo the previous operation, and treat all as y values multiplying by 1080/720. Any suggestions on to how to implement the spreadsheet routine?

Re: Converting Windows Form Data

Posted: Sun Feb 28, 2016 9:13 pm
by MrProgrammer
Hi, and welcome to the forum. The feature you need is Text Import as explained in [Tutorial] Text to Columns. You will need to learn how to use it.

• Step 0: Copy your ancient Windoze Form Data from wherever it is to the clipboard.
• Step 1: Click step 1's cell A1. Edit → Paste Special → Unformatted Text → Fixed Width → Click column 16 (in 10 . . . * .) → OK. Select column B. Edit → Copy.
• Step 2: Click step 2's cell A1. Edit → Paste. After you paste, click the row header (A above row 1). Data → Text to Columns → Separated by → Comma → OK.
• Step 3: Use =IF(ISTEXT(Step2.B1);Step2.C1;Step2.B1) in step 3's cell A1. Fill the formula down and right as needed.
• Step 4: Use =Step3.A1*$H$1 in step 4's cell A1. Fill the formula down and right as needed. Review the conversion factors in H3:H8 (I guessed). Click cells F1 and G1 to change the resolution and direction.
201602281234.ods
(10.38 KiB) Downloaded 119 times
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.

[Tutorial] Ten concepts that every Calc user should know

Re: Converting Windows Form Data

Posted: Mon Feb 29, 2016 11:50 am
by lmstearn
Hi MrProgrammer,
Thank you so much for the kind response and solution. I actually was a dab hand at Excel in the early 90s, but not having used it since it's kind of like starting all over again. Well almost :)
But there was nothing like Text to Columns then. Well done. :D
The original issue was actually using AOO after running the Home Theater Calculator. The paste and paste/special menu functions were greyed out!
There was also an issue with JRE rendering the help search unavailable.
Got a little confused at step 1
(Click column 16 (in 10 . . . * ↓ .)
.
Is that click column 16 in the Fields section?
And then at Step 2:
After you paste, click the row header (A above row 1).
Isn't the first row header the number 1 on the left? Or is it column A to be selected? Edit NVM: Don't use toolbars- The "A" is from this picture: please disregard....
Hmm but now what went wrong here- selected row 1 but the "text to Column" is greyed out for some reason?.....

Your file works perfectly,- but compare column B in steps 2 and steps 3, the values are different. No matter, can fix that in 2 secs: see the attachment- also my oops it was 768p not 720p. :)

Re: Converting Windows Form Data

Posted: Mon Feb 29, 2016 8:08 pm
by MrProgrammer
lmstearn wrote:Got a little confused at step 1 … Is that click column 16 in the Fields section?
[color=#FF00FF]Text to Columns tutorial (third paragraph)[/color] wrote:For the Fixed Width option, click on the dialog's ruler to set or clear field position breaks
Click at the top of the red dot to create a second field beginning in column 16. Calc will show the dot after you click.
Screen Shot 2016-02-29 at 11.22.50 .png
lmstearn wrote:Isn't the first row header the number 1 on the left? Or is it column A to be selected?
It's the A in the row of letters above row 1 of your data. Or, equivalently, the header above column A. You found it in the picture that you linked.
lmstearn wrote:selected row 1 but the "text to Column" is greyed out for some reason?
[color=#FF00FF]Text to Columns tutorial (third paragraph again)[/color] wrote:Make a contiguous selection of cells from exactly one column, then select menu Data → Text to Columns.
Clicking the A will select the entire column. Please review the tutorial.
MrProgrammer wrote:• Step 3: Use =IF(ISTEXT(Step2.B1);Step2.C1;Step2.B1) in step 3's cell A1.
lmstearn wrote:but compare column B in steps 2 and steps 3, the values are different
Oops. I should have used =IF(ISTEXT(Step2.$B1);Step2.C1;Step2.B1).
lmstearn wrote:There was also an issue with JRE rendering the help search unavailable.
Search for your symptoms (forum search, web search). If you can't find anything, open a new topic. This one is about converting form data.

If this solved your form data 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.

Re: Converting Windows Form Data

Posted: Tue Mar 01, 2016 7:34 am
by lmstearn
@MrProgrammer,
Thanks. We're almost there, but please note the values in columns A & C are x values and the values in columns B & D are y values. To make it work add an XandY block like this and include it in the range of the above selection lists:

Code: Select all

768p	 XandY	1
1080p	XandY	Err:508
2160p	XandY	Err:508
4320p	XandY	Err:508
Include the range in the formula (btw couldn't find any other examples of SUMPRODUCT used this way but it works!):

Code: Select all

=SUMPRODUCT($F$3:$F$14=F1;$G$3:$G$14=G1;$H$3:$H$14)
And now we have the pseudo code say for 1080p XandY in column H as:

Code: Select all

=1920/1368*($A$1:$A$18,$C$1:$C$18);1080/768($B$1:$B$18,$D$1:$D$18)
But cannot find any examples of applying a function to a range of cells this way. What's the real code for it?

Re: Converting Windows Form Data

Posted: Tue Mar 01, 2016 8:07 pm
by MrProgrammer
lmstearn wrote:We're almost there, but please note the values in columns A & C are x values and the values in columns B & D are y values.
Try a 3-column VLOOKUP table, with column 2 for X conversion values and column 3 for Y conversion factors. I used SUMPRODUCT since I didn't understand that your form data had mixed X and Y values. VLOOKUP does not work as well for two criteria (resolution and X/Y). Your situation is simpler because you have only one criteria, the resolution. Instead of $H$1, use VLOOKUP(…;…;2;0) in columns A and C and VLOOKUP(…;…;3;0) in columns B and D.
lmstearn wrote:But cannot find any examples of applying a function to a range of cells this way. What's the real code for it?
You don't want to do that. Read about relative references in section 8. Using formulas and cell references in Ten concepts that every Calc user should know. You won't have much success with spreadsheets until you understand absolute versus relative references. Put the formulas in the first row and fill down.

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.

Re: Converting Windows Form Data

Posted: Wed Mar 02, 2016 8:56 am
by lmstearn
Hi MrProgrammer,
Thanks for the help and the Tutorial on VLOOKUP. All sorted now. Thanks!