topic Re: Filling partially missing data cells based on non-missing cells in Discussions

Filling partially missing data cells based on non-missing cells

KMJHU — Thu, 18 Jul 2019 18:07:34 GMT

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

Column 1	Column 2	Column 3
1	a	a'
2	b	b'
1	a	a'
3	c	c'
2
3
1
3	c	c'
2

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!

Re: Filling partially missing data cells based on non-missing cells

KMJHU — Thu, 18 Jul 2019 18:20:50 GMT

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!

Re: Filling partially missing data cells based on non-missing cells

Mark_Bailey — Thu, 18 Jul 2019 20:02:24 GMT

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][1];
		Column(3)[] = map[value 1][2];
	);
);

So this is meant to run as a script, not as a column formula.