Get rid of empty cells while using formula

[nicholes]

i want to fill data in credit account of my customer automatically when his name is entered
for example my customer name is "A" so i use this formula
i make a helper sell in which i put cutomer name "A" in L1 so here is my formula

=IF(B3:B13=$L$1,A3:A13,"")

i have got this simple formula but it leaves spaces when customer name does not comes
(L1 is customer name's cell)

i want to get rid of those empty cells which comes because of "" . so how do i do this?

[galvinpaddy]

Hi, try

=IF($L$1="","",IF(B3:B13=$L$1,A3:A13,""))

[nicholes]

Hi, try

=IF($L$1="","",IF(B3:B13=$L$1,A3:A13,""))

well thanks for the reply
it works same as mine.here is what i need

when i write diffrent name of customer for example it is "B" nothing happened.(and it should be) but that "B" gives blank space in cells where i put this formula=IF($L$1="","",IF(B3:B13=$L$1,A3:A13,""))

OH those blank cells!!!!!
EDIT:- i have uploaded a screenttttttttttttttty.pngshot

the colored blank spaces in D4 D5 etc.

[Bob Phillips]

Try

=IF(ISERROR(SMALL(IF($B$3:$B$13=$L$1,ROW($B$3:$B$13)),ROW(A1))),"",
INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$L$1,ROW($B$3:$B$13)),ROW(A1))-MIN(ROW($A$3:$A$13))+1))

[nicholes]

Try

=IF(ISERROR(SMALL(IF($B$3:$B$13=$L$1,ROW($B$3:$B$13)),ROW(A1))),"",
INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$L$1,ROW($B$3:$B$13)),ROW(A1))-MIN(ROW($A$3:$A$13))+1))

i am putting your formula in F3 and drag it down but it gives answer for first (F3) and other are nothing but blank cells.

hy! Thanks for the kind help, but i need more PLEASE PLEASE PLEASE....

[Bob Phillips]

Did you array-enter it, Ctrl-Shift-Enter?

[tigeravatar]

nicholes,

Attached is an example workbook based on the screenshot provided and criteria described.
In cell D1 is a drop down list containing A and B.
In cell D3 and copied down to D7 is this array formula. Array formulas must be confirmed with Ctrl+Shift+Enter and not just enter:

=IFERROR(INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$D$1,ROW(INDIRECT("1:"&COUNTA($A$3:$A$13)))),ROW(1:1))),"")

A simpler alternative solution that provides the same results, is a pivot table, which is shown in column G.

[nicholes]

Thanks! BTW excel needed a lot of programming skills it is very hard to understand the formula.well, finally my problem is solved
thanks again

[arlu1201]

Nicoles,

As per the forum rules, your title is not descriptive of your issue. Your title helps others to locate answers to similar questions that they have. For you future posts, please ensure you comply with the rules. I will change it for you this time.

[nicholes]

Nicoles,

As per the forum rules, your title is not descriptive of your issue. Your title helps others to locate answers to similar questions that they have. For you future posts, please ensure you comply with the rules. I will change it for you this time.

i thought i would be a simple formula only.thus i thought i needed one new very simple formula only.

BTW i would take care in future THANKS!

[nicholes]

is there any wayt to do this without using "array" formula??

also i want to send these entery(price) in sheet 2

[tigeravatar]

nicholes,

As stated and shown in my earlier post and example workbook, you can use a pivot table, no formulas needed.

[nicholes]

Thanks for the replay

BTW can you explain your formula a little please (i am trying to get it in my mind) why small function is used there?

=IFERROR(INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$D$1,ROW(INDIRECT("1:"&COUNTA($A$3:$A$13)))),ROW(1:1))),"")

[tigeravatar]

The formula creates an array of position numbers that match the criteria. If B3:B13 = D1, then get the position of that cell. B3 would be position 1, B4 is position 2, etc. Index loads A3:A13 into an array, and the small determines which position in that array to use. the last argument, Row(1:1) will return the number 1, and as the formula is copied down, that last argument becomes Row(2:2) which will return the number 2, etc. That last argument tells small which position number to return.

That was kind of a dense explanation, let me try using an example:
A Values.....BValues
.....A..........1
.....B..........2
.....C..........1

So if the B values = 1, then load their positions into an array. In this case, it will be position 1 and position 3, so it looks like {1,3}
Small gets the number at the position specified (becasue the numbers are increasing, small 1 returns the smallest, small 2 returns the second smallest, etc):

Small({1,3},Row(1:1)) -> Small({1,3},1) -> 1
Small({1,3},Row(2:2)) -> Small({1,3},2) -> 3

Index loaded the A Values into an array, and then using the positions, returns the appropriate one:
Index({A,B,C},1) -> A
Index({A,B,C},3) -> C

Hopefully that helps. Sorry if that just made it more confusing.

[nicholes]

The formula creates an array of position numbers that match the criteria. If B3:B13 = D1, then get the position of that cell. B3 would be position 1, B4 is position 2, etc. Index loads A3:A13 into an array, and the small determines which position in that array to use. the last argument, Row(1:1) will return the number 1, and as the formula is copied down, that last argument becomes Row(2:2) which will return the number 2, etc. That last argument tells small which position number to return.

That was kind of a dense explanation, let me try using an example:
A Values.....BValues
.....A..........1
.....B..........2
.....C..........1

So if the B values = 1, then load their positions into an array. In this case, it will be position 1 and position 3, so it looks like {1,3}
Small gets the number at the position specified (becasue the numbers are increasing, small 1 returns the smallest, small 2 returns the second smallest, etc):

Small({1,3},Row(1:1)) -> Small({1,3},1) -> 1
Small({1,3},Row(2:2)) -> Small({1,3},2) -> 3

Index loaded the A Values into an array, and then using the positions, returns the appropriate one:
Index({A,B,C},1) -> A
Index({A,B,C},3) -> C

well i am getting the formula now, great job! i am really thankful to you for your kind help.

Hopefully that helps. Sorry if that just made it more confusing.

actually i am feeling not good, people like you help us and also say sorry to us! you are so kind to me

THANKS. (actually i am a shameful guy who ask too much, even to explain the formula. i should say sorry to you)

sorry to ask too much and take take your time.

CHEERS.