I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

I have saved it to my system, there is no such error. It has been saved
and opened withour any error.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

Click "View this thread" URL

"JLatham" wrote:

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

JLatham wrote:
I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:
This is due to a bug in excelforum. All attempts to get whoever it is that
supposedly looks after excelforum to fix it have fallen on deaf ears.

Change ?postid=4886 to ?attachmentid=4886 and it'll work



I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
Filename: Sample.zip |
Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=551813

One way ..

Try something along these lines

In 1.1,

Placed in C6, array-entered with CSE*:
=INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$E$6:$E$15=$E$3),0))
C6 is then copied down

*press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

Similarly, in 1.2,

Placed in C6, array-entered wih CSE:
=INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$G$6:$G$15=$E$3),0))
C6 then copied down

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.
Infact I enter subject codes of ACCA (which are from 1.1 to 1.3, 2.1 to
2.6 and 3.1 to 3.7) and there is no such way that I could enter them in
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them as
follows.
Reg # Name Father (subjects/Sections)
A1106 GGG FAGGG 2.3 2 2.4 1 2.5 1
so in sheet 2.3 at the left of this Reg # (i-e A1106) I want 3 as
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.5
at the left of same Reg # I want 1 as section number of that particular
subject.
I elaborate it more, suppose in sheet 1.2 all students which are pasted
in 1.2 sheet do not necessarity have this subject in Main sheet in 2nd
blue column this might be in 1st blue column for some students. Same is
the case for all subjects that in subject sheets some of students may
have that particular subject in first blue column (col E), some may
have it in second blue column and some may have it in third and some
may have that subject code in fourth blue column.

I think its more elaborative now and will help to determine the exact
formula.

thanks for working Max.

Max Wrote:
One way ..

Try something along these lines

In 1.1,

Placed in C6, array-entered with CSE*:
=INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$E$6:$E$15=$E$3),0))
C6 is then copied down

*press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

Similarly, in 1.2,

Placed in C6, array-entered wih CSE:
=INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$G$6:$G$15=$E$3),0))
C6 then copied down

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.

One way to extend it to cover cols E, G, I, K
[& to extract from the corresponding cols F, H, J, L] ..

Select cell C6 in sheet: 1.1, then group all sheets: 1.1 to 3.7 (hold down
SHIFT & select the rightmost sheet: 3.7), then array-enter in C6, and copy
down as far as required:

=IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$E$6:$ E$15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$G$6:$G $15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$I$6:$I $15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$K$6:$K $15=$E$3),0)),"",
INDEX(Main!$L$6:$L$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$K$6:$K$15=$E$3),0))),
INDEX(Main!$J$6:$J$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$I$6:$I$15=$E$3),0))),
INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$G$6:$G$15=$E$3),0))),
INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$E$6:$E$15=$E$3),0)))

[ Above formula is all within a single cell, C6 ]

Then right-click Ungroup sheets
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:
These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.
In fact I enter subject codes of ACCA (which are from 1.1 to 1.3, 2.1 to
2.6 and 3.1 to 3.7) and there is no such way that I could enter them in
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them as
follows.
Reg # Name Father (subjects/Sections)
A1106 GGG FAGGG 2.3 2 2.4 1 2.5 1
so in sheet 2.3 at the left of this Reg # (i-e A1106) I want 3 as
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.5
at the left of same Reg # I want 1 as section number of that particular
subject.
I elaborate it more, suppose in sheet 1.2 all students which are pasted
in 1.2 sheet do not necessarity have this subject in Main sheet in 2nd
blue column this might be in 1st blue column for some students. Same is
the case for all subjects that in subject sheets some of students may
have that particular subject in first blue column (col E), some may
have it in second blue column and some may have it in third and some
may have that subject code in fourth blue column.

I think its more elaborative now and will help to determine the exact
formula.

thanks for working Max.

Line:
.. then group all sheets: 1.1 to 3.7

should read as:
.. then group all sheets: 1.1 to 2.6

(Adapt the grouping to suit your actual sheets of identical structure)

Here's an implemented sample file:
http://cjoint.com/?gox5JhoyGh
Sample_1.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks, to you and Paul Lauterman below for that info. I had clicked on the
first link next to Download: and hit the error. Since the game is over now,
I'll go back to sleep. But I'll remember the problem at excelforum.com in
the future.

"Toppers" wrote:

Click "View this thread" URL

"JLatham" wrote:

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

thank you Max I will try to understand it because its a lengthy
formula.

however thanks for working.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
thank you Max I will try to understand it because its a lengthy
formula. however thanks for working.

You're welcome. Pl test it out at your end.

The "lengthy" formula is essentially an extension of the earlier,
with nested IFs used to iterate the checks/returns through
all 4 columns that inputs are likely (in cols E,G,I,K),
with corresponding returns (from cols F,J,H,L)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

would you please explain it for me that how each part of formula works
and return what? I'll be grateful to you.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
would you please explain it for me that how each part of formula works
and return what? I'll be grateful to you.

Did it work well for you ?
Pl confirm.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Infact it will take some time to implement this formula in my original
workbook that contains large data and references are also not the same
as were in the sample file.
I am also working on another formula for the same purpose and that is
not an array. I will share it with you after I get success to complete
that formula.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

Here's some key explanations ..

Typical core array expression:
MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3 ),0))

The lookup_array part: (Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3)
is essentially a product of 2 conditions: = Cond1 x Cond2
which will produce a resultant array of zeros with a single 1,
eg:{1;0;0;0;0;0;0;0;0;0}
where the "1" indicates the row position satifying both conditions (Cond1 &
Cond2)

[Cond1: where Reg# criteria satisfies, Cond2: where subject code criteria
satisfies,
Cond1 x Cond2: where both criteria satisfy]

MATCH(1,{1;0;0;0;0;0;0;0;0;0},0))
then matches the "1" to the array above to return the row position within
the array

INDEX(Main!$F$6:$F$15,MATCH(...))
then returns the contents of the cell within F6:F15 corresponding to the row
position returned by MATCH

The nested:
=IF(ISNA(MATCH(1),IF(ISNA(MATCH(2),...
INDEX(...,MATCH(2))),INDEX(...,MATCH(1)))
will check through in sequence, the possibility of any inputs for the
subject codes: 1.1, 1.2, etc being made within either of cols E,G,I,K
and if so, the corresp INDEX(...,MATCH(..)) will then return the required
result from either of cols F,H,J,L

The implicit assumption of course, is that there will not be any duplicate
subject code inputs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:

Infact it will take some time to implement this formula in my original
workbook that contains large data and references are also not the same
as were in the sample file.
I am also working on another formula for the same purpose and that is
not an array. I will share it with you after I get success to complete
that formula.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

thank you very much Max for your time and effort. I will try it now on
my orginal workbook but it will take some time as I am also working on
another formula which also seems to be a long formula but that is not
an array.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

here is the file and first part of formula which I am working on.


+-------------------------------------------------------------------+
|Filename: Sample-2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4901 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
thank you very much Max for your time and effort. I will try it now on
my original workbook but it will take some time as I am also working on
another formula which also seems to be a long formula but that is not
an array.

Would suggest you try implementing the earlier array formula <g, which
should work fine and is, I believe the simpler/shorter route in this
instance. cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I will try to implement both formulas but then I check for excel
calculation speed. I have many other formulas in that workbook as well,
some of which are arrays. Infact arrays slow down speed that's why I
avoid to use them. I have to implement this formula in most of my
sheets and in many rows and the reference data is also large. Array
will definitely slow down speed of calculation therefore I have to have
a substitution of this.
I will appreciate if you help me to complete that formula.

Thank you in anticipation

Regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

In sheet: 1.1,

Put in C6:
=IF(OR(D6="",$E$3=""),"",
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P1",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S1",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P2",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S2",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P3",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S3",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P4",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S4",match col,0)),"")))))
C6 copied down

[C6:C18's formulas can be entered simultaneously into all other identically
structured sheets via grouping the sheets, as explained in previous response.
But do remember to ungroup the sheets immediately thereafter]

Here's the implemented sample:
http://cjoint.com/?gsplvpiSJd
Starguy_Sample2.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:

I will try to implement both formulas but then I check for excel
calculation speed. I have many other formulas in that workbook as well,
some of which are arrays. Infact arrays slow down speed that's why I
avoid to use them. I have to implement this formula in most of my
sheets and in many rows and the reference data is also large. Array
will definitely slow down speed of calculation therefore I have to have
a substitution of this.
I will appreciate if you help me to complete that formula.

Thank you in anticipation

Regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

thank you very much Max. I really appreciate you that you helped me.
thank you again.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
thank you very much Max. I really appreciate you that you helped me.
thank you again.

You're welcome, Starguy!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

There is another problem which I want to tackle.
I want that students's data be entered in only Main sheet and then it
should automatically be pasted in subject sheets (in subjects which are
in blue columns in Main for each student). Students' data should be
pasted in subject sheets in last empty row.
e.g if a student choses 1.1, 1.2 and 1.3 then it should automatically
be pasted in sheets 1.1, 1.2 and sheet 1.3 in last empty row.

is there any way?

Regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
There is another problem which I want to tackle.
I want that students's data be entered in only Main sheet and then it
should automatically be pasted in subject sheets (in subjects which are
in blue columns in Main for each student). Students' data should be
pasted in subject sheets in last empty row.
e.g if a student choses 1.1, 1.2 and 1.3 then it should automatically
be pasted in sheets 1.1, 1.2 and sheet 1.3 in last empty row.

Perhaps this non-array formulas set-up would achieve it ..

Refer sample construct at:
http://cjoint.com/?gutPFjL50M
Starguy_Sample3.xls

In Main,

List across in N5: 1.1, 1.2, 1.3, 2.1 etc
Then in N6, copied across, filled down:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(N$5,$E6:$L6))))0,ROW(),"")

Then in 1.1,

In D6, copied across to F6, filled down to F15:
=IF(ISERROR(SMALL(OFFSET(Main!$M$6:$M$15,,MATCH($E $3,Main!$N$5:$Z$5,0)),ROW(A1))),"",
INDEX(Main!B$6:B$15,MATCH(SMALL(OFFSET(Main!$M$6:$ M$15,,MATCH($E$3,Main!$N$5:$Z$5,0)),ROW(A1)),
OFFSET(Main!$M$6:$M$15,,MATCH($E$3,Main!$N$5:$Z$5, 0)),0)))

Repeat (or copy paste) the formulas in 1.1 in the other sheets: 1.2, 1.3,
2.1 etc

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

thank you Max
can I copy this formula in Main to the right for more subjects like
2.2, 2.3 and so on and same way in those sheets.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

Thanks for that info. I finally figured it out, someone else told me to
click the 'View this thread' link and that worked for me.

At least they permit/enable file uploading, which certainly facilitates
giving assistance in 9 out of 10 cases around here.

"Paul Lautman" wrote:

JLatham wrote:
I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:
This is due to a bug in excelforum. All attempts to get whoever it is that
supposedly looks after excelforum to fix it have fallen on deaf ears.

Change ?postid=4886 to ?attachmentid=4886 and it'll work



I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
Filename: Sample.zip |
Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=551813