[Solved] Converting Windows Form Data

[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?

[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 117 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

[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.
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.

[MrProgrammer]

Got a little confused at step 1 … Is that click column 16 in the Fields section?

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.

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.

selected row 1 but the "text to Column" is greyed out for some reason?

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.

• Step 3: Use =IF(ISTEXT(Step2.B1);Step2.C1;Step2.B1) in step 3's cell A1.

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).

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.

[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?

[MrProgrammer]

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.

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.

[lmstearn]

Hi MrProgrammer,
Thanks for the help and the Tutorial on VLOOKUP. All sorted now. Thanks!