Formatting Problem in Formula

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

We have used a formula in column G, but when we check the Cell Format of any E5:F16 its coming

Untitled.png


but when we check the cell format of G5:G16 it is coming the following

Untitled2.png


Problem is how can we get the Cell Format setting as per first image in column G

please find the excel file also.

xlsx file as u have told.xlsx
EFG
4AmountBatchTesting
53000 
63200 
73000,3200,85008500 
84000 
92000 
104000,2000,30003000Flag
117654 
123000,8000,40004000Flag
136000 
144000,6000,30003000Flag
155000 
166000Flag
Sheet1
Cell Formulas
RangeFormula
G5:G16G5=IF(COUNTIF($G$4:G4,LOOKUP(2,1/SEARCH(F5,$E$4:E4),ROW($E$4:E4)))>0,0,IFERROR(LOOKUP(2,1/SEARCH(F5,$E$4:E4),ROW($E$4:E4)),""))
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
Hi there, could you explain what your formula is trying to achieve?
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Yes its trying to find the E7 three value in column F but from the F8 (any one amount) and after that it will stop working and again we will put the new value in column E and it will start the doing same thing again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web

ADVERTISEMENT

thanks Fluff ji,

may be it will work in my case,

by the way my main problem is -
we are getting the Flag but when we try to put any formula in Column E10 its not taking is a Flag, you can try the following code in E10
VBA Code:
=IF(OR($B10=3,$G10="Flag"),Get4Numbers($F$6:$F10),"")



Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
I can't try that formula, because it uses a UDF, which I don't have.
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web

ADVERTISEMENT

Pls find the UDF

Code:
Function Get3Numbers(Batch As Range)

Dim T As Integer, X As Integer
Get3Numbers = ","

For T = 1 To WorksheetFunction.Min(3, Batch.Rows.Count)
  If IsNumeric(Batch.Cells(Batch.Rows.Count - X, 1)) Then
    
    If InStr(1, Get3Numbers, "," & Batch.Cells(Batch.Rows.Count - X, 1) & ",") = 0 Then
    Get3Numbers = "," & Batch.Cells(Batch.Rows.Count - X, 1) & Get3Numbers
    Else
    T = T - 1
    End If
  
  End If
X = X + 1

Next T

If Get3Numbers <> "" Then Get3Numbers = Mid(Left(Get3Numbers, Len(Get3Numbers) - 1), 2)

End Function

I can't try that formula, because it uses a UDF, which I don't have.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
You are right, what can we do that in that case, we need any text

The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,153
Office Version
  1. 365
Platform
  1. Windows
Maybe
=IF(OR($B10=3,$G10<>""),Get4Numbers($F$6:$F10),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,114,083
Messages
5,545,858
Members
410,711
Latest member
Josh324
Top