Hi, I have been looking for awhile now, but cant find anything that i can make work.
In the attached file, I am trying to list all the dates that a specific entry was made. The data is A1:I20 and I hand-made my results in D27:U28.
In Column I the entries are 1,2, or 3(with 3 indicating both 1 and 2)
Sorry if this an inefficient layout. I am trying to collect data from 2 different sources, and make it useful to me.
Last edited by Befuddled; 08-21-2011 at 06:36 AM.
Try this in E27
In F27
Please Login or Register to view this content.
Drag both down.Please Login or Register to view this content.
Similarly for the other ranges.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
That works great, Any ideas on how to get the dates to generate themselves, from the data?
Sorry you beat me back to the board.
This would be better
In E27
Drag Across to F27 then Down.Please Login or Register to view this content.
Back soon after I look at your last post.
Oops, just noticed that all the dates in my example were different, in the real workbook, there will be multiple occurences of each date, for many different die#s. Isn't the formula you gave me just checking the date to the entry in I?
Correct, still trying to find a way.
Is it also only for D:D = 121 or 125?
Last edited by Marcol; 08-21-2011 at 07:19 AM.
Well, I was just trying to make an example.
My actual sheet has 2000-4000 rows of entries in it. But not all of them would have 121 or 125 in column D.
Rows 27-28 would be on a separate sheet, and copied down 350 times to account for all the different Die #s with approx.
Would something based on this workbook suit your needs?
1/. Create a dynamic named range for your die numbers
Name:=
"Unique_Dies"
Refers To:=
2/. Insert a column at the start of your sheetPlease Login or Register to view this content.
Then in A2
Drag/Fill Down to suitPlease Login or Register to view this content.
3/. In Column I enter a series of numbers 1 to suit (step 1) in this example = 20
4/. Use the name "Unique_Dies" as Data Validation in J2
5/. In K2 this array formula
Confirm with Ctrl + Shift + Enter not just EnterPlease Login or Register to view this content.
Drag Down to suit
6/. In L2 this array formula
Confirm with Ctrl + Shift + Enter not just EnterPlease Login or Register to view this content.
Drag Across to M2 then Down to suit
Select from the drop-down in J2
Note there is a more efficient way than these arrays for larger data tables, I can't remember it off-hand, but will find it if this method works for you
I see how I could make that work, but it is was making a list of ALL entries with that Die# in Column D, not just the entries with "121" or "125" in Column E so I changed A1 to:
=IF(D2="","",COUNTIFS($D$2:$D2,$D2,$E$2:$E2,"121")+COUNTIFS($D$2:$D2,$D2,$E$2:$E2,"125"))
But for some reason, when I use the dropdown for Column E, and select only "121" and "125", the entry for 8/17/11 disappears?
Is there another way to make it work, when I put it on a separate sheet(as I will have a sheet for each month). other than making 15 columns for each month, and making each set of columns search through the sheet for that month.
I attached a sample of how i would set it up
Last edited by Befuddled; 08-23-2011 at 12:10 AM.
Bump No Response
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks