Collate and Index Info

[adeassessor]

Hi I am new to this site, and not an expert when it comes to using Excel... which is why I'm here and hoping you guys may be able to help me :-)


Is there an easy way in which Excel can collect and collate information/data? This data can sometimes be in form of text, as well as numerical value.

For example, my worksheet comprises of modules (Units, if you will) which each features its own list of criteria. As seen in my attached example spreadsheet. When an entry covers criteria from a given unit it is represented by "Y". If not, it is left empty. The first thing I need is a formula that can collate all these "Y"'s and list them in one cell, but converting the Y to the value of the criteria it is covering. (Refer to example sheet for better understanding of this!)

On the example, there are three sheets: Main, Unit 1 and Unit 2.

Main is used to contain all the collected data for each and every entry and all the units it has covered.

Unit 1 and Unit 2 sheets are examples of how the entries should be collated, listed and indexed as per unit coverage. (Again refer to example).

The example spreadsheet merely shows how it SHOULD work (a static if you like - there are no formulas as that's the help I need). :-)

If there is a SIMPLE and EFFECTIVE way of doing this that you guys can help me with, GREAT! Many thanks for your help :-)

[pike]

hI adeassessor,
Welcome to the forum
Try the attched file

[adeassessor]

I have tried looking at the first problem - collating all criteria from multiple cells into a single cell. I have turned the 'Y" references into the values to match the corresponding criteria/column it falls under. Then I have applied the TRIM formula to collate the results.



However, I still need to collate the results (entries) into their designated units AND I need the reference to come back as text "Y"!!???

Have attached updated example FYI.


Thanks again :-)

[adeassessor]

Hi Pike,

Many thanks for your help :-)


I can see what you have done and it kinda does what I am looking for, however, the data from the Main sheet should be producing the data on the Unit 1 and Unit 2 sheet - not the other way round :-)


Have just posted updated regarding this part of problem - I came across TRIM which will allow me to collate data from multiple cells, but it means I will have to use values instead of "Y" for each criteria.



Still need the spreadsheet to collate/index the data from Main into Unit 1 and Unit 2 though.... so any more suggestions on this would be handy :-) Thanks again!

[pike]

Hi adeassessor

Nearly there

need to do some work on
=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Main!$A$2:$A$30,MATCH(TRUE,INDEX(ISNA(MATCH(Main!$A$2:$A$30,$A$1:$A1,0)),0),0))))
so not to show if there are no values in the column
anyway have a look at this attachment

[pike]

Hi adeassessor

Nearly there

need to do some work on
=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Main!$A$2:$A$30,MATCH(TRUE,INDEX(ISNA(MATCH(Main!$A$2:$A$30,$A$1:$A1,0)),0),0))))
so not to show if there are no values in the column
anyway have a look at this attachment

[martindwilson]

pike here's a hand

server cahnge over error

here it is fixed

server change over error

[pike]

LOL you always quick of the mark.

[adeassessor]

Hi Pike,


This is great stuff... so far, everything is working just as I need it... I have made a few tweaks to get things to appear as I need it.... like changing "Y" to "P" and changing font to Wingbats 2 to get a tick....




Any more help with the entries not appearing on the Unit sheets would be massive massive help :-)



Have attached new update #3.

[pike]

Bummer, havent been able to get it with out a helper column

[pb71]

Something like this in A2 in of the Unit 1 worksheet to get around the helper column:


=IFERROR(INDEX(Main!A:A,SMALL(IF(LEN(INDEX(Main!$B$2:$B$20&Main!$C$2:$C$20&Main!$D$2:$D$20&Main!$E$2:$E$20&Main!$F$2:$F$20,0))>0,ROW(Main!$A$2:$A$20)),ROW(A1))),"")

...confirmed with Ctrl+Shift+Enter

[adeassessor]

hmmm.... ok now Entry 2 doesn't seem to appear in Unit 2 sheet??

Not liking the helper column though.... any way of this working without having this?

[pb71]

Switch the formulas in columns B to F between sheets Unit 1 and Unit 2.

Please see attached.

You need to use:

=IFERROR(INDEX(Main!A:A,SMALL(IF(LEN(INDEX(Main!$H$2:$H$20&Main!$I$2:$I$20&Main!$J$2:$J$20&Main!$K$2:$K$20&Main!$L$2:$L$20,0))>0,ROW(Main!$A$2:$A$20)),ROW(A1))),"")

...confirmed with Ctrl+Shift+Enter in cell A2 of the Unit 2 sheet

...then drag down

[adeassessor]

WOW GUYS!!!!! THIS IS FANTASTIC!!!

Thank you thank you... this is going to make such a big impact on how I calculate/analyse results :-)


May I ask one final question..... what if... I wanted to have the Unit sheets (Unit 1 and Unit 2) in a different/separate worksheet.... is that possible without making any major changes to the coding already in place???

If it can't be done, its not a major issue... thanks guys :-)

[pb71]

Do you mean new workbooks?

Open a new workbook. Copy and paste the Unit 1 sheet into a worksheet in the new workbook:

When you close the source workbook you will see the formulas in the destination workbook appended with the file path of the source workbook.

E.g. cell A2 in the new workbook with the source workbook example2 adeassessor4.xlsx saved to C:\:

=IFERROR(INDEX('C:\[example2 adeassessor4.xlsx]Main'!A:A,SMALL(IF(LEN(INDEX('C:\[example2 adeassessor4.xlsx]Main'!$B$2:$B$20&'C:\[example2 adeassessor4.xlsx]Main'!$C$2:$C$20&'C:\[example2 adeassessor4.xlsx]Main'!$D$2:$D$20&'C:\[example2 adeassessor4.xlsx]Main'!$E$2:$E$20&'C:\[example2 adeassessor4.xlsx]Main'!$F$2:$F$20,0))>0,ROW('C:\[example2 adeassessor4.xlsx]Main'!$A$2:$A$20)),ROW(A1))),"")

Save the new workbook as Unit 1 (in this case), for example.

[adeassessor]

Brilliant!! Thanks guys once again.... I wish I could work this sort of stuff out myself... I am soooo dumb.... you guys are way cool!!! Thanks again :-)

[pike]

Hi adeassessor

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[adeassessor]

Hi guys....

I really thank you in advance for any help you could offer :-)

I have a small issue which is really frustrating me... I have attached an example spreadsheet FYI. This is a continuation of an earlier problem posted a few days ago, which PIKE was able to help me with (thanks again PIKE) :-)

I have copied/pasted/tweaked the calculations PIKE provided me into my working spreadsheet, but something isn't working properly...

In the sheet labelled 'Candidate' is a comprehensive database which records all my learners progress on a particular qualification they are working towards. As a candidate submits a new piece of work, this is entered into the database and the assessor then 'ticks' which areas have been completed - full list of criteria can be found from column ref CB onwards (each qual is made up of units, which in turn comprises of its own list of criteria, hence why the database is soooo long/comprehensive). As the assessor marks off each criteria, this then SHOULD automatically update a matrix sheet for each unit (subject) covered (I have only added 1 of these units as an example, which is on the sheet labelled 'AG1').

When you look at sheet 'AG1', you can see I have entered the relevant formula's so that the relevant information appears in their required cells... however, it all works fine for the first row of information, but nothing appears from row 9 downwards (as highlighted in yellow)!??

This has been racking my brain and I can only think of one thing that may be causing the problem. If you look in cell ref B7 there is a formula set up. Take a look at the end of the formula you will see a bit that says: ....)),ROW(A1)))... I am not even sure what this is referring too??? I have tried changing this reference to different cells both in the same sheet as well as in different sheets, but still nothing appears!?? At the moment I have used the same cell ref as the formula in the cell above it, but its showing 'Witness Account'..... What it SHOULD show is the corresponding evidence title from the sheet 'Candidate' - which in fact is called Observation - (example given on sheet aptly named WHAT IT SHOULD SHOW).

Any suggestions would be really appreciated :-)

[pb71]

The formulas are array formulas and need to be confirmed with Ctrl+Shift+Enter. Once you have done this in cells B7 to D7 on the AG1 worksheet, drag the formulas down for the rest of the range. The SMALL function is being used in the formula. ROW(A1) equates to 1, ROW(A2) equates to 2, so you are finding the 1st smallest value, then the 2nd smallest value, etc. (in this case the row number in the Candidate worksheet) if cells CB:CT on the Candidate sheet aren't all empty in a row. This row value is then used in the INDEX function to return the corresponding value in column E on the Candidate worksheet for the formula in column B on the AG1 worksheet.

[adeassessor]

thank you thank you thank you thank you!!!!

Its the small things that please so well ;-)