Taking a list, and Making another list
I am looking for a formula to take a list of teams in row a, and copying them in a list under a month - it would pick the proper team based on their arrival date.
I have attached a spreadsheet with the worksheet, and the what I am looking the final output to be.
I am not sure how to get it so the list just continues without spaces - I don't want gaps between team names.
Any insight would be greatly appreciated.
Thanks,
Chuck
I used a helper column (C) to create unique ID's for each month. In C2 copied down is
=TEXT(B2,"mmm")&"_"&SUMPRODUCT(--(LEFT($C$1:$C1,3)=TEXT(B2,"mmm")))+1
That column can be hidden.
Then, I changed your headers to match 3 character month abbreviations (i.e. August = Aug)
and in E2 copied across and down
=IFERROR(INDEX($A$2:$A$7, MATCH(E$1&"_"&ROWS($A$1:$A1), $C$2:$C$7,0)),"")
See attachment
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Without helper columns, enter this ARRAY formula in D2 and fill down and across as required:
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
The key to this formula is matching the months in column B with the column headers. They must match in order for the formula to work. The ranges listed are to accommodate the 30 teams in the NHL.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
is it possible to have it so it doesn't match the column headers, but maybe a static field that states the month in it?
The matching has already been taken care of in the formulae that I used in the solution given. You just don't see it without looking for it.Originally Posted by cdeguzman
Is it possible to get it so that it works if you skip cells? For example - I have a worksheet that skips lines (formatted for page breaks, and custom top rows) - the file is to big for the site - but if you email me [email protected] i will send you a sheet to show you.
Essentially I have a header after the page break that is different to other pages, and I want to skip those cells and continue on. See attached - The HEADER should be skipped and the "final" tab is what I'd like it to look like.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks