Log In
Register
Home
New Member
January 21, 2020
Offline
Hi,
I am looking to generate a list on a new worksheet that pulls data from Column A if Column B has data input. If there is no data in Column B I want the formula to skip all those rows and inly output the value in Column A if there is data in Column B.
I do not want to use Pivot Table for this for a variety of reasons. My knowledge has me looking at Index/Match or Vlookup formulas. Thanks in advance for your help.
July 24, 2019
Offline
it is a ARRAY formula (STRG + SHIFT + ENTER). copy it and drag it down
=IFERROR(INDIRECT("A"&SMALL(IF(B:B="";"";ROW(A:A));ROW(A2)));"")
Trusted Members
December 20, 2019
Offline
Assuming i have set up the data okay (its always better to attach example data so we can see what we are looking at) you have a couple of options here but, in my opinion, the formula is fairly complex.It also depends what version of Excel you are running
In the future you will be able to use the filter function, but this is currently only on Office365 insider. However it is by far the best way
=FILTER(A2:A9,B2:B9="x")
Personally i think advanced filter is the simplest for Excel 2016 and older - you need to select the data table, the criteria (in the same format) and the output location.
https://www.myonlinetraininghu.....ue-records
The formula is an array formula so needs ctrl-shift-enter
=IFERROR(INDEX($A$2:$A$9,SMALL(IF("x"=$B$2:$B$9,ROW($A$2:$A$9)-1),ROWS($E$1:E1))),"")
You could also create a quick macro if you are doing it regularly or on lots of work sheets.
Purfleet
VIP
Trusted Members
June 25, 2016
Offline
Just some minor suggestion to Purfleet's answers. We don't know what is in column B (may not be an x).
Better to check for non-blank instead of x.
=IFERROR(INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9<>"",ROW($A$2:$A$9)-1),ROWS($E$1:E1))),"")
As for the advanced filter, the criteria can be changed from x to <>
Again to check for non-blank.
This also applies for the FILTER function.
Hope this helps.
Sunny
1 Guest(s)
