Filling of cells

[Candykobli]

Hi, 

I am cleaning a dataset, and I noticed that there are some cells with a null value while other cells have some value under the same condition. For example:

Country	GDP
Germany	10000000
Germany	Null
Germany	10000000
France	20000000
France	Null

I need to replace the null value with the GDP of each country, like this:

Country	GDP
Germany	10000000
Germany	10000000
Germany	10000000
France	20000000
France	20000000

How can I do that? There are many countries in this situation.

Thank you for your help.

[DataNath]

Hey @Candykobli, there's a few ways you could do this i.e. taking a max with a Summarize and re-joining. However, I think this is a little simpler:

1) Sort values (within [Country]) in descending order so the non-null value is always first

2) Use a Multi-Row Formula to fill down

Hope this helps!

[ed_hayter · Accepted solution]

I opted for a filter out Nulls create a lookup table with a GDP for every country that is nonnull (Logic was to take the first value but you could use different logic if you have a choice of values). Then join onto the NULL cases and replace Null GDP with the populated one. Union the filled data with the original data used for the lookup table for the expected outcome

[Candykobli]

Thank you very much for your help. I really appreciate. You saved my life🤗. Thanks a lot!

[FinnCharlton]

If you love macros you'll love this solution

[DataNath]

Here's another way of going about it where we just take the count of records and the max GDP per country (so it removes the nulls). After that, we just generate the number of rows necessary for each Country:

[Candykobli]

All your solutions work very well. Thank you again!