Fill cells based on historical data

mirmad

New Member
Joined
Nov 11, 2015
Messages
12
Hello,
once again me with a specific problem that I am not able to solve yet. Not sure if this can be tackled easily, I searched the net for some solutions and tried myself using my limited knowledge, but no luck so far. I will appreciate anyone's helps on this.

I have a table with A-M columns that receives new rows every day (tables added at the end). New rows contain only data in columns A-M, old rows contain also data in columns N-P.
I need to automatically add cells in columns N-P to a new row if value in M exists in any of the previous rows. Those values should be the same as the first entry found in the rows above.
If such entry is not found then cells N,O,P should be inserted with 'n/a'
In example
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]A4[/TD]
[TD]A5[/TD]
[TD]A6[/TD]
[TD]A7[/TD]
[TD]A8[/TD]
[TD]A9[/TD]
[TD]A10[/TD]
[TD]A11
[/TD]
[TD]A12
[/TD]
[TD]A13
[/TD]
[TD]A131
[/TD]
[TD]A242
[/TD]
[TD]A353
[/TD]
[/TR]
[TR]
[TD]B2
[/TD]
[TD]B2
[/TD]
[TD]B3
[/TD]
[TD]B4
[/TD]
[TD]B5
[/TD]
[TD]B6
[/TD]
[TD]B7
[/TD]
[TD]B8
[/TD]
[TD]B9
[/TD]
[TD]B10
[/TD]
[TD]B11
[/TD]
[TD]B12
[/TD]
[TD]B13
[/TD]
[TD]XXX
[/TD]
[TD]YYY
[/TD]
[TD]TTT
[/TD]
[/TR]
[TR]
[TD]C3
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]C5
[/TD]
[TD]C6
[/TD]
[TD]C7
[/TD]
[TD]C8
[/TD]
[TD]C9
[/TD]
[TD]C10
[/TD]
[TD]C11
[/TD]
[TD]C12
[/TD]
[TD]A13
[/TD]
[TD]A121
[/TD]
[TD]A232
[/TD]
[TD]A343
[/TD]
[/TR]
[TR]
[TD]D4
[/TD]
[TD]D2
[/TD]
[TD]D3
[/TD]
[TD]D4
[/TD]
[TD]D5
[/TD]
[TD]D6
[/TD]
[TD]D7
[/TD]
[TD]D8
[/TD]
[TD]D9
[/TD]
[TD]D10
[/TD]
[TD]D11
[/TD]
[TD]D12
[/TD]
[TD]D13
[/TD]
[TD]A131
[/TD]
[TD]YYY
[/TD]
[TD]EEE
[/TD]
[/TR]
[TR]
[TD]E5
[/TD]
[TD]E2
[/TD]
[TD]E3
[/TD]
[TD]E4
[/TD]
[TD]E5
[/TD]
[TD]E6
[/TD]
[TD]E7
[/TD]
[TD]E8
[/TD]
[TD]E9
[/TD]
[TD]E10
[/TD]
[TD]E11
[/TD]
[TD]E12
[/TD]
[TD]E13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]A13
[/TD]
[TD]A121
[/TD]
[TD]A232
[/TD]
[TD]A343
[/TD]
[/TR]
[TR]
[TD]7b
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]B13
[/TD]
[TD]XXX
[/TD]
[TD]YYY
[/TD]
[TD]TTT
[/TD]
[/TR]
[TR]
[TD]8c
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD][/TD]
[TD][/TD]
[TD]g
[/TD]
[TD]fs
[/TD]
[TD]g
[/TD]
[TD][/TD]
[TD][/TD]
[TD]da
[/TD]
[TD][/TD]
[TD]C13
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[/TR]
[TR]
[TD]9d
[/TD]
[TD]DDD
[/TD]
[TD]FW
[/TD]
[TD]FS
[/TD]
[TD]FE
[/TD]
[TD]FR
[/TD]
[TD]FT
[/TD]
[TD]FZ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10e
[/TD]
[TD]ds
[/TD]
[TD]gr
[/TD]
[TD]ht
[/TD]
[TD]jz
[/TD]
[TD]ku
[/TD]
[TD]vd
[/TD]
[TD]gr
[/TD]
[TD]ht
[/TD]
[TD]sw
[/TD]
[TD]ce
[/TD]
[TD]ge
[/TD]
[TD]D13
[/TD]
[TD]A131
[/TD]
[TD]YYY
[/TD]
[TD]EEE
[/TD]
[/TR]
</tbody>[/TABLE]

First five rows are the old records - the cells N to P are filled up already or not filled up.
Second five rows are added to the sheet, depending on the value in column M the cells to the right (N,O,P) should be filled, based on the first finding above with the same value in column M.
That results in:
Row 6 has A13 in the column M. First cell with same value above the current(new) row is in 3rd row - cells N,O,P from third row therefore get copied and inserted into row 6
Row 7 has B13 in the column M. First cell with same value above the current(new) row is in 2rd row - cells N,O,P from third row therefore get copied and inserted into row 7
Row 8 has C13 in the column M. There is not a cell with this value in the rows above, so 'n/a' is inserted in the cells N,O,P
Row 9 has E13 in the column M. First cell with same value above the current(new) row is in 5th row - it contains nothing in cells N,O,P, therefore row 9 shall also contain nothing in cells N,O,P
Row 10 has D13 in the column M. First cell with same value above the current(new) row is in 4th row - cells N,O,P from 4th row therefore get copied and inserted into row 10

I see that the problem may be how to recognize the old rows - if that is so I can replace blank N,O,P cells with any string to mark old rows as the ones where N,O,P contains something.

Has anyone experience with such methods of filling up cells?

many thanks in advance for anyones help.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
See if this, copied across and down is any use to you.

Excel Workbook
ABCDEFGHIJKLMNOP
1A1A2A3A4A5A6A7A8A9A10A11A12A13A131A242A353
2B2B2B3B4B5B6B7B8B9B10B11B12B13XXXYYYTTT
3C3C2C3C4C5C6C7C8C9C10C11C12A13A121A232A343
4D4D2D3D4D5D6D7D8D9D10D11D12D13A131YYYEEE
5E5E2E3E4E5E6E7E8E9E10E11E12E13
66aaaaaaaaaaaaA13A121A232A343
77bxxxxxxxxxxB13XXXYYYTTT
88cfggfsgdaC13n/an/an/a
99dDDDFWFSFEFRFTFZE13
1010edsgrhtjzkuvdgrhtswcegeD13A131YYYEEE
Fill Cells
 
Upvote 0
Peter, you are the best. This was really easy for you while so complicated for me:), the formula works exactly as I wanted, thanks again and have a great day!:)
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top