Formula Help - Please

Question

Hi,I am struggling.I am trying to figure out a formula that accomplish the following results:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Formula AnswerRow 1&nbsp; &nbsp; &nbsp;4301-01&nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Row 2&nbsp; &nbsp; &nbsp;4301-02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12Row 3&nbsp; &nbsp; &nbsp;4301-03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp;Row 4&nbsp; &nbsp; &nbsp;4301-04&nbsp; &nbsp; &nbsp;14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14Row 5&nbsp; &nbsp; &nbsp;4301-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14Row 6&nbsp; &nbsp; &nbsp;4301-06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14

sergeibaklan · Answer

Hi Tom,
&nbsp;
That could be formula
=IF( RIGHT(A2,2)*1=1, IF(ISBLANK(B2),"",B2), IF(ISBLANK(B2),C1,B2))
or Power Query which is more preferable if you have thousands of updating records
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddThirdColumn = Table.AddColumn(Source, "C", each
        if Text.End([A],2) = "01" then
            if [B] = null then "" else [B]
        else [B]),
    FillDown = Table.FillDown(AddThirdColumn,{"C"})
in
    FillDown
Alternatively you may use PQ to connect to your SQL database and made transformation within that query returning final result.
&nbsp;
Both variants are in attached

ahmad ali · Answer

Hi, Why don't you just apply FILL DOWN on column B?

tom54887 · Answer

I am sorry my original case did not reflect exactly what I am trying to accomplish.I have a added few more rows of data. I am dealing with an excel table with thousands of records that gets automatically refreshed from a SQL Server Database.&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Formula AnswerRow 1&nbsp; &nbsp; &nbsp;4301-01&nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Row 2&nbsp; &nbsp; &nbsp;4301-02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12Row 3&nbsp; &nbsp; &nbsp;4301-03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp;Row 4&nbsp; &nbsp; &nbsp;4301-04&nbsp; &nbsp; &nbsp;14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14Row 5&nbsp; &nbsp; &nbsp;4301-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14Row 6&nbsp; &nbsp; &nbsp;4301-06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14Row 7&nbsp; &nbsp; &nbsp;4302-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Row 8&nbsp; &nbsp; &nbsp;4302-02&nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp;Row 9&nbsp; &nbsp; &nbsp;4302-03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5

tom54887 · Answer

Thank you so much.This is my first time coming to this site for help. I have struggled on my own many times for hours. It is so nice to know there is help out there.Thanks again.

sergeibaklan · Answer

You are welcome

Forum Discussion

Formula Help - Please

5 Replies