I have 3 lists with different student names and a total number of tokens each student has been awarded each week. I would like to know a formula that puts every students name from the 3 list into a single list and in an adjacent column count the total number of token won by each student for all weeks.
excel forum.jpg
Make in column H a list of all student names (you probably have it already - if not, copy all columns A C E in H and remove duplicates - it is located on Data ribbon).
then in I2 array formula (comitted with Ctrl+Shift+Enter):
and copy down.
Please Login or Register to view this content.
PS. attached sample workbook is usually worth 100 screenshots :-)
Best Regards,
Kaper
I like the (committed with Crtl+Shift+Enter) function. ill have to learn more about it. However, i'm wondering if there is a way to create the student list and remove duplicates automatically. (Without Macros). The picture i attached is only a small example of a much larger list and to copy each column will be difficult.
Hi,
Instead of a picture, could you post an actual workbook illustrating what you require?
Obviously replace any confidential/sensitive information with dummy data if necessary.
Regards
you will have to forgive me ignorance. i have no idea how to add a workbook. I saw the insert picture function and figures that would work.
Click on Go Advanced and scroll down until you see Manage Attachments.
Regards
I recorded a macro and assigned it to a button....see if this type of solution will work for you.
Attachment removed....lost contents on upload.
Recreated attachment....just to give you an idea.
Last edited by newdoverman; 03-16-2014 at 07:58 PM.
<---------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
I Hope this works. So the formula for Column I works and is great but i need to List in column H to be created Automatically since this is only a small sample and the full lists will be very extensive and further columns will be added in future weeks.
Attachment 304803Attachment 304803
Hmmm. Invalid attachment?
There is an example here:
http://www.excelforum.com/excel-form...ml#post3438143
See how complicated that formula is?
If you were able to put the data in a single list something like below, it would be easier by orders of magnitude!
Data Range
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The idea is to make the data easy to use and not necessarily pretty. Reports can be pretty and fairly easily created if the data is in an easy to use format like Tony Valko shows.
wow that is complicated. i might just have to get in and do it manually. its a shame cos I feel there should be something there. Its not the first time i've wanted to do something like this. Thanks for all your help.
something like this?
the neat "straggle" trick is Tony Valko's.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
that seems to work actually. I will hopefully use that to get it working in the main workbook. Cheers
Nice one!
Here's a slightly different version of the same basic technique that will even work in Excel 2003 and earlier.
Array entered** in J2:
=LOOKUP("zzz",CHOOSE({1,2,3,4,5},"",INDEX(List4,MATCH(0,COUNTIF(J$1:J1,List4),0)),INDEX(List3,MATCH(0,COUNTIF(J$1:J1,List3),0)),INDEX(List2,MATCH(0,COUNTIF(J$1:J1,List2),0)),INDEX(List1,MATCH(0,COUNTIF(J$1:J1,List1),0))))
** 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.
Last edited by Tony Valko; 03-17-2014 at 10:05 AM.
This formula (in Cell J2) works well but how do you get the Array in the Index to say List1, List 2 etc. instead of $A$3:$A$10?
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(list1,MATCH(0,COUNTIF($J$1:$J1,list1),0)),INDEX(list2,MATCH(0,COUNTIF($J$1:$J1,list2),0))),INDEX(list3,MATCH(0,COUNTIF($J$1:$J1,list3),0))),INDEX(list4,MATCH(0,COUNTIF($J$1:$J1,list4),0))),"-")
Pmcca37_997009-gather-data-from-multiple-columns-and-put-them-into-one-list_16mar14_01.xlsx
Last edited by Pmcca37; 03-18-2014 at 06:04 AM.
You would create named ranges:
http://www.contextures.com/xlNames01.html
perfect ty so much
If we think about something more universal, may be a macro (as suggested earlier by newdoverman) would be a good concept.
Below is easy to adopt to different number of weeks example (as a matter of fact, also J2 could be determined automatically, but I left it as a constant here):
Please Login or Register to view this content.
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks