BookmarkSubscribe
Choose Language Hide Translation Bar
KMJHU
New Contributor

Filling partially missing data cells based on non-missing cells

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 1Column 2Column 3
1aa'
2bb'
1aa'
3cc'
2  
3  
1  
3cc'
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!

0 Kudos
2 REPLIES 2
KMJHU
New Contributor

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

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!
0 Kudos

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

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.

Learn it once, use it forever!
0 Kudos