Hello cloud crowd,
I need some help.
The attached data sample shows a few rows of data, with columns F to L in each row sporting form controls. The first one is a combo box, the other ones are check boxes.
I need to extract the value of each combo box and check box and put them in the prepared cells in columns P to V in their respective row.
The spreadsheet has been set up by someone who obviously had little understanding of form controls. None of the controls are linked back to a cell, so the only indication I have for the value of a form control is to look at it and read it.
In most cases, all controls on one row are grouped into one group, but throughout the spreadsheet I also have some rows where not all objects are grouped, and others, where none of them are grouped. In the example, row 9 contains a group that does not contain all objects of the row and row 11 is not grouped at all.
I basically need a script that ungroups all groups, reads the value of each control and writes the value into the respective cell in the respective column of the same row.
I don't mind if the result for the combo box is just the number of the selected item as it appears in the source list (like the value you'd find if the combo box were linked to a cell). If you can extract the text, that's great, but I'll be fine with just the number equivalent. I'm quite comfortable with Vlookup and Index/Match, but not quite there yet with the VBA required for this.
For the check boxes, I can live with a 1 for checked and 0 for not checked, or any other easy to interpret binary.
The real spreadsheet has about ten of these sheets, with around 1000 rows each, and a couple of hundred empty rows with empty form controls. The spreadsheet owner complains that the file runs slowly in 2010. I'm not surprised.
The file is 2003 format, obviously, but I'm happy to process solutions in later versions.
No rush, I'll be offline for most of the next 36 hours.