Question description:
This user has given permission to use the problem statement for this
blog.
I need to create a rows in Sheet 1 where Sheet 2 column c has values in Sheet 2 column D that are greater than .01 and leave out items in Sheet 1 where Sheet 3 column D are = 0 (without using a filter, then copy/paste).
Solved by I. Y. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/06/2018 - 08:22
Hello and welcome to Got it! :)
User
08/06/2018 - 08:22
Hello
User
08/06/2018 - 08:23
Did you see my question, or do I need to input?
Excelchat Expert
08/06/2018 - 08:23
I've read your concern, but I hope we could have a sample data we could work on to get a better grasp of the situation. :)
User
08/06/2018 - 08:24
OK. Let me input
Excelchat Expert
08/06/2018 - 08:24
Okay thanks! :)
User
08/06/2018 - 08:25
I want to show a list on Sheet 2 of items in column c where items in another column are greater than 0
User
08/06/2018 - 08:25
in my example, only row 3 would show up
User
08/06/2018 - 08:26
without using filters, copying and pasting
Excelchat Expert
08/06/2018 - 08:27
Oh Okay I see. Basically, you want to fetch data from column C if its corresponding cell in column D is greater than 0, correct? Basically, in your data, it should only be Airfare that will show up. Is this right? :)
User
08/06/2018 - 08:27
Yes
User
08/06/2018 - 08:27
Need to show that on sheet 2
Excelchat Expert
08/06/2018 - 08:28
Okay I see! Just to clarify, it's only data in column C that we need to get from the table? :)
User
08/06/2018 - 08:28
If I can understand how to list just those items........I can build the rest
Excelchat Expert
08/06/2018 - 08:28
Okay nice then! Let me work on it for a while then, and update you the soonest! :)
User
08/06/2018 - 08:29
cool.........thanks!
Excelchat Expert
08/06/2018 - 08:32
Already done! Please see sheet 2 for your review. :)
User
08/06/2018 - 08:33
Geez........I thought I was good with formulas
Excelchat Expert
08/06/2018 - 08:33
The formula used is an array formula combining INDEX, SMALL, IF and ROW functions of Excel. :)
User
08/06/2018 - 08:33
I saw this online, but couldn't quite follow the logic
Excelchat Expert
08/06/2018 - 08:34
I also incorporated an IFERROR statement so that when there's no more data that matches your criteria, it will return a blank, which is in the cells below. :)
Excelchat Expert
08/06/2018 - 08:34
Oh I see, okay. :)
Excelchat Expert
08/06/2018 - 08:34
I can try to explain if you wish to dissect the formula. :)
User
08/06/2018 - 08:34
So awesome!!!!
User
08/06/2018 - 08:35
I'd love to understand better if possible
Excelchat Expert
08/06/2018 - 08:36
Okay, sure thing! We start with the INDEX function that returns a cell value after we specify a row and column offset.
User
08/06/2018 - 08:36
so, index the column itself (range)
Excelchat Expert
08/06/2018 - 08:37
yes. :)
User
08/06/2018 - 08:37
then what does small do?
Excelchat Expert
08/06/2018 - 08:38
the SMALL function returns the "nth smallest" value depending on the user-defined "n"
Excelchat Expert
08/06/2018 - 08:38
For example, if we have =SMALL(A1:A10,5), it returns the 5th smallest value from the A1:A10 range. :)
Excelchat Expert
08/06/2018 - 08:40
It is in this SMALL that we specify the criteria of our data, which is that we will only consider to look at values in column C if their corresponding value in column D is more than 0.
User
08/06/2018 - 08:40
I'll have to look at that one closer
Excelchat Expert
08/06/2018 - 08:40
That is denoted by the =IF(0<Sheet1!$D$3:$D$457 statement. :)
User
08/06/2018 - 08:42
pretty complex..........I'll figure it out.......I usually do, but I needed to see it........
Excelchat Expert
08/06/2018 - 08:42
yes you can! It's actually a pretty simple formula once you understand how each function works. :)
User
08/06/2018 - 08:43
I'm content with the feedback, response, time, etc. Thank you so much for your help!
Excelchat Expert
08/06/2018 - 08:43
Nice! If all is good now, please do end the session via the button on the upper right of your screen (sorry, can't do it from our end). Please do leave a good feedback upon your exit. Thanks so much and have a nice day! :)
Excelchat Expert
08/06/2018 - 08:43
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
User
08/06/2018 - 08:44
cool!
User
08/06/2018 - 08:44
Thanks again. Have a great day!
Excelchat Expert
08/06/2018 - 08:44
You as well! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
