Hi
Greetings,
I have one excel workbook (attached) with cell B contains the "box no." and cell C contains the "boxes effected"
I want a formula to show in the remarks column of all the boxes which are effected as "Not Billed". I have around 46000 records to fill like this.
Regards,
VKKT
Excel Formula
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Excel Formula
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Excel Formula
Hi Mr. Hans,
Where ever the Nos in "Box No." matches with the Nos. in "Box Effected" then the remarks column will show as " Not Billed"
Regards,
VKKT
Where ever the Nos in "Box No." matches with the Nos. in "Box Effected" then the remarks column will show as " Not Billed"
Regards,
VKKT
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Formula
I don't understand the values in your sample workbook. Why is D2 blank while D3 has "Not billed"? Why is D16 blank?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Excel Formula
I assumed that you want to find Col B values in col C if found then “not billed” otherwise keep blank
=IF(SUMPRODUCT(--(B2=C:C))=0,"","Not Billed") or also can use like =IFERROR(IF(MATCH(B2,C:C,0),"Not Billed",""),"")
=IF(SUMPRODUCT(--(B2=C:C))=0,"","Not Billed") or also can use like =IFERROR(IF(MATCH(B2,C:C,0),"Not Billed",""),"")
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Excel Formula
Yes, this is the formula I am looking for.
Thanks Mr. Adeel for your help.
Sorry Mr. Hans for raising an unclear question.
Regards,
VKKT
Thanks Mr. Adeel for your help.
Sorry Mr. Hans for raising an unclear question.
Regards,
VKKT
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Formula
I'm glad that Adeel has solved your problem, but his formulas don't return the same result as column D in your sample workbook!
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Excel Formula
Greetings:
Mr. Hans Sorry for the incorrect sample.
Appreciate your continuous support.
Regards,
VKKT
Mr. Hans Sorry for the incorrect sample.
Appreciate your continuous support.
Regards,
VKKT