• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

adding a letter in cells

lateef

New Member
i have 1500 rows. I want to add the letter x in column B if the cell is blank in the same row data is available in column A
 

AlanSidman

Well-Known Member
In the attached, I have created a sample of data as described in your Post. I have employed Power Query to complete the task.

Mcode:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"x",Replacer.ReplaceValue,{"Column2"})
in
    #"Replaced Value"
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced





 

Attachments

lateef

New Member
Thanks for the replies. I have got the solution
=IF(AND(A1="",B1=""),"",IF(AND(A1<>"",B1=""),"x",B1))
 

Peter Bartholomew

Well-Known Member
The formula that produces the "X" is actually in column B. The column is 1 pixel wide, so the text is displayed in the empty column C.

69523

Note: the "@" is present because I use MS365. Older versions of Excel will reference columns A and C by implicit intersection.
 

Attachments

Top