I have a master data set on one sheet...
for example
Letter number
t---- 1
b---- 2
e ---- 3
d ---- 4
t ---- 5
r ---- 3
t ---- 9
I want another sheet to automatically populate a data set from the master data pertaining to "t" like this...
Letter number
t---- 1
t---- 5
t ---- 9
thats it.
Is this possible with a formula that can be autofilled down? Thanks.
(excuse my lack of knowledge when it comes to making a proper table of values for my illustration)
Last edited by tracker1833; 07-02-2013 at 08:20 PM.
How about a result like this:
T......1
........5
........9
Since you're "filtering" the data on "T" we already know that "T" is a common value to each result so there's no need to lookup both the "Ts" and the corresponding value. Just lookup the corresponding value.
Assuming your data is on Sheet1 in the range A2:B8...
t.....1
b.....2
e.....3
d.....4
t.....5
r.....3
t.....9
On some other sheet...
A2 = T
Enter this array formula** in B2:
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!A$2:A$8=A$2,ROW(Sheet1!A$2:A$8)),ROWS(B$2:B2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Toni,
Thanks for your reply and answer. I never worked with arrays, so thanks for the incite. Unfortunately, I couldn't get it to work with the formula you posted. It looks like it's close though. It is finding the first value that is "t" for sheet 1 and displaying that corresponding value in the other sheet. But the next array value is the same instead of displaying the next "t" corresponding value.
Here is an example
sheet 1
m....4
t....1
m....3
b....8
t....7
sheet 2 (display)
t....1
t....1
t....1
t....1
t....1
heres what I wanted
t....1
t....7
Looks like there may be a small error, but its close. Im trying to figure it out. Any thoughts? Thanks.
Make sure calculation is set to automatic.
Formulas tab>Calculation>Calculation Options>Automatic
Toni,
Your equation was correct. I used it wrong. I was highlighting all the cells and pressing ctrl alt enter instead of just the first cell and copying it down. Thanks a lot for your help. It works great!
I still dont really understand how it works though. Why did you need the small function?
We're telling the INDEX function to return the data from specific row numbers that meet the condition IF(Sheet1!A$2:A$8=A$2.
Where that condition is TRUE the corresponding row numbers are passed to the SMALL function. Then the SMALL function passes the nth smallest row number to the INDEX function for each cell that the formula is copied to and we get the expected result.
If you still need more explanation let me know and I'll give you the "deluxe" version! (it'll take a while to type it out)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks