Require multiple value from a horizontal table

December 21, 2016 at 02:07:20
Specs: Windows 7
Sir/Madam,

I have an excel horizontal table, and i want to serch multiple value from the table below:

1 2 3 4 5 6

A CL CL NA NA P

Now I want to find the values of CL or NA in separate cells, can you provide me the formula?

By: BIDYUT KUMAR DAS

message edited by BIDYUTKUMARDAS


See More: Require multiple value from a horizontal table

Report •

#1
December 21, 2016 at 06:12:43
First, a posting tip:

Please click on the link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.


As far as your question, I'm not sure what you mean by "find the values of CL or NA in separate cells". I see 2 occurrences of both CL and NA in your example but I have no idea what you are trying to do with them.

Please provide some more detail regarding your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2016 at 09:34:40
My table is as follows.

A B C D E F G
1 1 2 3 4 5 6 7
2 CL SL NA NA CL SL NA

Now I want to find multiple value of "NA" in new cells

message edited by BIDYUTKUMARDAS


Report •

#3
December 21, 2016 at 09:51:33
It does not appear that you followed the instructions in the link I referred you to. Had you followed those instructions, your data would have been lined up in columns like this, so it would be easier for us to read.


      A      B     C      D      E      F      G 
1     1      2     3      4      5      6      7
2     CL     SL    NA     NA     CL     SL     NA


As far as your question, I still have no idea what you are trying to do. You have not provided any more details than you did in your original post.

"Now I want to find multiple value of "NA" in new cells"

What do you mean by find the value of "NA" in new cells?

Please keep in mind that we have no idea what your goals or requirements are so you need to be very specific as to what your output should look like.

I see NA in C2, D2, and G2. What is it that you want to do with those values?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 21, 2016 at 10:03:21
You are right,
I want the value of C2,D2 & G2

Like:. NA(No Application) for = day 3,. day 4. & day 7

message edited by BIDYUTKUMARDAS


Report •

#5
December 21, 2016 at 10:16:04
OK, let me try to explain to you what you are asking for since you seem reluctant to explain it to me.

- You have data A2:G2.
- You want to find the cells within that range that contain NA
- You want to know on what "days" the NA's occurred

Have I guessed at that correctly?

If so, that is as far I can go, because:

- I do not know what you want in the "new cells" you mentioned.
- I don't know where those "new cells" are.
- I do not know what you want the output to look like.

Please use more words than you have been using in the past because you are not explaining your requirements in a way that I can understand them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 21, 2016 at 10:44:22
You guessed correctly. I want to keep employees leave status in a excel workbook, Row A1 is the days of a particular month and Row A2 is the employees leave application status. Now if I enter NA in cell A5 , cell B5, B6 & B7 should show multiple value of NA available in Row A1. Am I clear .

Report •

#7
December 21, 2016 at 11:07:22
No, you are not clear.

"Row A1 is the days of a particular month and Row A2 is the employees leave application status."

There is no such thing as Row A1 or Row A2. There is a Column A and a Row 1, and cell A1, but there is no Row A1.

So, I'll assume that you meant:

"Row 1 is the days of a particular month and Row 2 is the employees leave application status."

Then you said: "Now if I enter NA in cell A5 , cell B5, B6 & B7 should show multiple value of NA available in Row A1."

Didn't you say that Row 1 contains "days of a particular month"? How can there be multiple values of NA in Row 1 if Row 1 contains numbers?

Are you saying that you want to return the numbers from Row 1 that are associated with the NA's that are in Row 2?

In other words, based on the example data in Response #3, cells B5:B7 should contain 3, 4 and 7, respectively?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
December 21, 2016 at 11:27:58
I want to return the numbers from Row1 to cells B5:B7 associated to NA's in Row2.

cells B5:B7 should contain 3, 4 and 7, respectively


Report •

#9
December 21, 2016 at 12:00:52
Finally! That was harder than usual. ;-)

You will need an Array formula for this situation. An array formula is entered by pressing Ctrl-Shift-Enter, not just Enter. Brackets { } will appear around the formula indicating that it is an Array formula. You cannot just type the brackets, you have to use Ctrl-Shift-Enter. In addition, each time you edit the formula, you will have to use Ctrl-Shift-Enter.

Paste this into B5, use Ctrl-Shift-Enter and then drag it down to B11 to account for all 7 days in Row 1.

=IFERROR(SMALL(IF($A$2:$G$2=$A$5,COLUMN($A$2:$G$2)),ROW(1:1)),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
December 21, 2016 at 12:12:23
I don't have my main excel worksheet with me now, tomorrow I will give you the feedback. Thanks from the core of my heart , for your cooperation.

Report •

#11
December 22, 2016 at 03:51:41
It works fine, thanks for support.

Report •

#12
December 22, 2016 at 06:45:58
I'm glad it worked. There is one thing that I forgot to mention:

The formula I offered does not actually refer to the numbers in Row 1. You could delete them and the formula would still work. The numbers that the formula is returning are actually the column numbers of the sheet.

It is not finding the NA and returning the number in the cell above the NA, it is finding the NA and saying "I found NA in Column 3" which is the same thing as saying "I found NA in Column C".

The reference to ROW(1:1) is just a counter, not a reference to Row 1. If you look at the formulas that are created when you drag the formula down, you'll see that ROW(1:1) becomes ROW(2:2), ROW(3:3), etc.

Bottom line: The numbers in Row 1 have absolutely nothing to do with the results of the formula. They are not referenced or even required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
December 22, 2016 at 07:12:05
When I enter the formula using different column name I.e. F instead of A then I observed that it is not actually returning the value of NA instead it is returning the column number. Then at the end of the formula I just used (-) to reduce the no of column I have used in my formula .

If I could have share my worksheet with you, I could have asked for some more help, which I am unable to explains in letter.

Thanks a lot. GOD BLESS YOU.
If

message edited by BIDYUTKUMARDAS


Report •

#14
December 22, 2016 at 07:30:53
Your use of the minus operator is the perfect solution.

You can do the same thing with the ROW() and COLUMN() functions.

=ROW() will return the number of the Row in which the function is used. e.g if you enter =ROW() in Q37, it will return 37.

Therefore, if you were to use the formula I suggested in A5, you could replace the ROW(1:1) "counter" with Row()-4 which would also return "1" and get incremented as you drag it down.

Using ROW(1:1) has the advantage of returning 1 regardless of where you use it (i.e. as a simple counter) but the ROW() - x technique can be very useful in other situations.

The same holds true for =COLUMN()

re: "If I could have share my worksheet with you"

You can share files by posting them at zippyshare.com and then posting the link in this forum. Be sure to remove any personal/confidential data beforehand.

If you have questions that are not related to the question in this thread, please start a new thread with a relevant subject line. That allows the archives to be searched more easily.

With the holidays coming up I will be on the road visiting family, so I won't be spending too much time checking the forum for the next few days.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
December 22, 2016 at 08:27:49
Can I get the result in a single cell?

And I also want to omit the null value in my result, please help me.


Report •

#16
December 22, 2016 at 08:33:43
I don't know what you mean by "omit the null value in my result".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
December 22, 2016 at 08:56:44
Now I am entering the formula in say ten cells to get the value of NA from the column number, when nothing to return in tenth cells it is returning #null value, that I want to omit. The cell shoul be blank, where there is nothing to return

Am I clear?

message edited by BIDYUTKUMARDAS


Report •

#18
December 22, 2016 at 09:08:09
I'm still confused. Let's go back to your example data where we should see 3, 4 & 7 in the B5, B6 & B7.

If I drag that formula down to the bottom of the sheet I get nothing but blank cells below B7. If I put NA in all cells in $A$2:$G$2, I'll get the Column number in B5:B11 and all blanks below that.

Maybe I don't know what you mean by "returning null value" or don't understand the actual issue you are having. The formula has to return something. As written it returns "" once all the NA have been found. "" is basically a blank cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
December 22, 2016 at 09:53:26
I am very poor in English, but still I am trying to explain my problem.

I have changed the formula to get the result horizontally, and entered the formula in 31 cells in a Row to get the status for a particular employee, that which are day of the month he availed leave, say he availed leave for 3,5,9,11,14 day of the month, remaining row 31 cells I entered the formula is now returning "#____" result.

I require those cells should be blank only.

From my horizontal table I want to return the day of the month an employee was on CL, PL, SL, NA & A(absent) , I actually want for all these type of leave ,the result should appear in a separate cell for each type of leave


Report •

#20
December 22, 2016 at 10:35:04
I think the best idea would be for you to set up a worksheet of "fake" employees and upload it to zippyshare.com

Trying to work through this without the actual workbook, especially since you have modified the formula, is getting us nowhere.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
December 22, 2016 at 10:44:25
It is a good idea, tomorrow I will share the fake worksheet.

Report •

#22
December 22, 2016 at 10:55:01
As I noted earlier, don't expect much of a response over the holiday weekend.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#23
December 22, 2016 at 11:01:06
Kept in mind, and no hurry I can wait to get a better solution.

Report •

Ask Question