I'm fairly certain I have seen this somewhere before, but I can't find the formula now. I have a spreadsheet of names, id numbers and other info; I want to create a list to show matching info, for instance, three people have a code of a###, and three have a code of b###, but I know that there will never be more than 40 people with any letter suffix of the code. Is there a formula that I can copy down 40 rows to list out the A*, then change it to list out 40 rows of B*?
Last edited by WebKill; 03-30-2011 at 03:32 PM.
Post up a sample workbook showing a manual mockup of your desired results, as well as the sheet of raw data you are referencing. Click GO ADVANCED and use the paperclip icon to post up your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There we go, I posted an example; of course the actual data is much larger, but the goal is to have another sheet with the A series and B series stuff on it so that it’s a pre-formatted and ready to print page.
This is a pretty standard INDEX/MATCH scenario. This is my favorite of the lookup formulas and it's probably the most versatile.
I moved your A Series and your B Series tables to the second sheet. Then put this formula in D2 and copied across the tables:
=INDEX(Sheet1!A:A, MATCH($D2,Sheet1!$D:$D, 0))
I colored the cells that do not have formula, those are your static "codes". Fill out those columns with your base codes, all the other cells "match" to those codes on Sheet1, wherever they may be, and bring over the matching data.
I like the INDEX/MATCH as well, but this is not quite what I was looking for; I basically want "A*" so that it will pull whatever A code pops up on the first sheet.
Like I said, not sure if this can be done or not, but I thought I had seen it before where the formula would take all the matching or 'Duplicate' values (since A* would return many results) and then fill in, and based on looking at what it has already filled in given the range it is using, fill in the next cell with a different person with an A code and so on.
I've added an array formula into the colored region to automatically collect all the "A" values and the "B" values respectively, in the order they appear. This makes sheet2 fully automated.
Array formulas, when edited, are confirmed by pressing CTRL-SHIFT-ENTER to reactivate the array, then you can copy the cells.
That did it, thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks