Filling partially missing data cells based on non-missing cells
Jul 18, 2019 11:07 AM(170 views)
Hello, I'm trying to figure out a way to fill in missing cells that should have values that are based on other cells. In my table, some of these cells are properly filled in, while others aren't. Here's a rough example
In this example, every row that has a '1' in the first column should have an A, and then an A' in the 2nd and 3rd column, respectively. The same goes for 2's needing to have B and B', and 3s needing to have C and C'. However, in some parts of my table, that stops happening. Is there any way I can automatically fill in missing cells based with their correct data, based on the value provided in the first column, and the information that WOULD be in the cell, were it not missing? Thanks!
edit: In my real table, there are thousands of potential values in column 1, so I can't manually find all rows with the column value and input what I know to be the column 2 and 3 data. What i would need is a formula that goes back and finds the correct information from previously, fully filled out rows, and automatically finishes the row based on the value in column 1. Thanks again!
I cannot test this script but at least it illustrates one approach.
Names Default to Here( 1 );
map = Associative Array();
For Each Row(
value 1 = Column(1);
value 2 = Column(2);
value 3 = Column(3);
If( And( Not( Is Missing( value 2 ) ), Not( Is Missing( value 3 ) ) ),
// complete row, add to map
map << Insert( value 1, Eval List( value 2, value 3 ) ),
// incomplete row, use values in map
Column(2) = map[value 1];
Column(3) = map[value 1];
So this is meant to run as a script, not as a column formula.