Need attendace sheet for saleman visited diffrent regions with different Items

In C20 copied across and down:

=IF(FILTER($E$4:$E$14,($C$4:$C$14=$A20)*($D$4:$D$14=$B20))=C$19,"P","")

In C20

Formula:

Please Login or Register  to view this content.

Copy acroos and down

Not easily, and it takes under three seconds do the drag copy across and down - I'm not in the mood to spend an hour trying to work out how to save you two seconds, sorry!

OK, don't waste time and spend time for helping others; you are doing great job. It is a two second job for a sample book but it will slow down my a long worksheet when excel have to recalculate each cell. Any how, thanks for your solution

Actually, I did have a go.

=LET(r,DROP(REDUCE(0,A20:A30&B20:B30,LAMBDA(x,y,VSTACK(x,FILTER(E4:E14,C4:C14&D4:D14=y)))),1),DROP(REDUCE(0,C19:M19,LAMBDA(x,y,HSTACK(x,IF(y=r,"P","")))),,1))

Great Work !!! You are Genius

Nah - I'm a nerd, though.

Still not enough, then?

=LET(r,DROP(REDUCE(0,tocol(A20:A3000,1)&tocol(B20:B3000,1),LAMBDA(x,y,VSTACK(x,FILTER(E4:E14,C4:C14&D4:D14=y)))),1),DROP(REDUCE(0,C19:M19,LAMBDA(x,y,HSTACK(x,IF(y=r,"P","")))),,1))

The ranges are static and i have to adjust if i add new region or new entry for salesmen

I don't understand the problem, sorry.

Sorry i ignore your reply with ToCol() function. Now it is dynamic.....Thanks

Just make the ranges longer than you'll ever need:

=LET(r,DROP(REDUCE(0,TOCOL(G4:G10000,1)&TOCOL(H4:H10000,1),LAMBDA(x,y,VSTACK(x,FILTER(TOCOL(E4:E10000,1),TOCOL(C4:C10000,1)&TOCOL(D4:D10000,1)=y)))),1),DROP(REDUCE(0,TOROW(I3:ZZ3,1),LAMBDA(x,y,HSTACK(x,IF(y=r,"P","")))),,1))

YOu have to do some of the work!

Is it possible to sum/count "P" based on salesman and output might be like following

REG-1 REG-2 REG-3 REG-4 REG-5 REG-6 REG-7 REG-8 REG-9
Salesman101 1
Salesman111 2
Salesman112 2
Salesman113 2
Salesman114 2
Salesman115 5
Salesman116 2
Salesman117

You are going to need to explain this (and tell me how you calculated the percentages):

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	K	L	M	N	O	P	Q
19		REG-1	REG-2	REG-3	REG-4	REG-5	REG-6
20	Salesman101	33%		67%
21	Salesman111	11%		56%		11%	22%
22	Salesman121		50%		50%
23
24		15%	17%	41%	17%	4%	7%

Sheet: Sheet1

Where did salesman 121 come from???

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

@aliGW

Calculation of percentages are mentioned in the sample Excel. If Salesman101 is dealing in three Items, then, for each region, the % is 1/3=33%. Salesman111 is dealing with 9 items, therefore, the region wise % is 1/9=11%. Region wise accumulated percentage is then calculated which is mention in your query. Salesman121 is an arbitrary entry to make the table more self-explanatory.

Adding a salesman that doesn't exist does NOT make anything more self-explanatory - it merely adds to the confusion!!!

Are you on the BETA channel for Excel 365?

If you are on the BETA channel:

=LET(v,VSTACK(B19:H19,GROUPBY(A20:A31,C20#,SUM)),IF(v=0,"",v))

OK - if no GROUPBY, then try this:

Please Login or Register  to view this content.

In future, please remember that it is ONE issue per thread here, please. Thanks.

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

@AliGW, Why doesn't the 11% from cell G31 end up in cell P28?

Try this:

Please Login or Register  to view this content.

@AliGW, Why doesn't the 11% from cell G31 end up in cell P28?

See the correction to the formula above - the BYCOL array had been set incorrectly.

Now it is. A good improvement. .

Not an improvement - a correction. Brain fart - I was up too early! The dawn chorus always wakes me, and it's getting much earlier now ...

How I add reputation with this message (You must spread some Reputation around before giving it to AliGW again.)

It tells you - until you have repped someone else, you won't be able to rep me again. Don't worry about it.

No idea what you are on about this time.

Your expected output above and mine below - where are the headings incorrect???

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	K	L	M	N	O	P	Q
19		REG-1	REG-2	REG-3	REG-4	REG-5	REG-6
20	Salesman101	33%		67%
21	Salesman111	11%		56%		11%	22%
22
23
24		15%	0%	41%	0%	4%	7%
25
26		REG-1	REG-2	REG-3	REG-4	REG-5	REG-6
27	Salesman101	33%		67%
28	Salesman111	11%		56%		11%	22%
29		15%		41%		4%	7%

Sheet: Sheet1

Is this what you want?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	K	L	M	N	O
31		REG-1	REG-3	REG-5	REG-6
32	Salesman101	33%	67%
33	Salesman111	11%	56%	11%	22%
34		15%	41%	4%	7%

Sheet: Sheet1

If so, then this:

Please Login or Register  to view this content.

OK - I'm out, sorry. You have now shifted the goalposts in this thread THREE TIMES!

In future, explain EXACTLY what you need IN FULL in your opening post.

No - it's to do with you wanting to add extra columns.

I have made a few changes.

In A31:

=TOCOL(C4:C28,1)

In B31:

=TOCOL(D4:D28,1)

In C31:

=LET(r,DROP(REDUCE(0,A31#&B31#,LAMBDA(x,y,VSTACK(x,FILTER(TOCOL(E4:E28,1),TOCOL(C4:C28,1)&TOCOL(D4:D28,1)=y)))),1),IFNA(DROP(REDUCE(0,TOROW(C30:Z30,1),LAMBDA(x,y,HSTACK(x,IF(y=r,1/COUNTIFS(A31#,A31#),"")))),,1),""))

In N3:

Please Login or Register  to view this content.

I will look at generating the headers in row 30 automatically later.

In A30:

=HSTACK("Salesmen","Items","REG-"&SEQUENCE(,MAX(IFNA(--TEXTAFTER(E4:E28,"-"),0))))

Now it's fully automated.

And if you want only columns with values in the summary table:

Please Login or Register  to view this content.

You're welcome,

Think EVERYTHING through first. AND try to wiork it out yourself first based on what you've learnt so far.

I was curious to see if I could refine it further.

Table 1 in A30:

Please Login or Register  to view this content.

Table 2a in N3:

Please Login or Register  to view this content.

Table 2b in Z3:

Please Login or Register  to view this content.

@Ali: in at the very deep end for me but can you clarify for me what x,y z are in the following:

Formula:

Please Login or Register  to view this content.

As I undertand it,(LAMBDA) x is a parameter, as y and z (?) but to what do they refer ?

For example the "a" could be replaced C4:C28 (from the LET

The videos I have watched on LAMBDA are much simpler to understand than the above

Yes, sure.

=REDUCE("",c,LAMBDA(x,y,VSTACK(x,BYCOL(DROP(A30#,1,2),LAMBDA(z,SUMIF(a,y,z))))))

REDUCE requires TWO parameters: an opener (usually "" or 0) plus in this case the range defined by c.

LAMBDA(x,y,VSTACK(x,BYCOL(DROP(A30#,1,2),LAMBDA(z,SUMIF(a,y,z)))))

LAMBDA then needs to give a name to these two paramters - I use x and y.

Then you need to stack x on top of y:

VSTACK(x,BYCOL(DROP(A30#,1,2),LAMBDA(z,SUMIF(a,y,z))))

BUT you are going to manipulate y:

BYCOL(DROP(A30#,1,2),LAMBDA(z,SUMIF(a,y,z)))

In the nested LAMBDA, z refers to the range A30#.

All of these sub-parameters in LAMBDAs take a bit of getting used to, but suddenly, one day, you've got it. Took me ages!

Does this help?

This construct is used where you might otherwise want to nest a BYCOL within a BYROW (or vice-versa), which is not possible.

For example the "a" could be replaced C4:C28

You are trying to make the formula as concise as possible bt defining parameters, especially if they are to be used multiple times.

@AliGW Thanks, I login to ask the same issue but you fix it before my query.


The "Data Missing" msg appears which is good when any one item/Region entry is missing but it should be like for only those salesmen where item(s) or Region are missing...Anyhow you make an excellent solution for my problems.

@Ali: thank you for taking the time to explain the elements of the formula: it will take a while (if ever)! to get my head round these functions within functions!

I thought that only a few weeks ago, and then suddenly, I seemed to get it.

Thanks, I login to ask the same issue but you fix it before my query.

I read your mind ...

However, you should have been able to add the IFERROR trap yourself, really.

I have no idea what you mean, but all three columns HAVE to be completed for the formulae to work.

the output is not generated until there there is no missing value under Item or region column.

Correct - as I said, the formula needs all three columns complete. Why would I have expected values to be missing when that has never been shown in your sample data? Is this another bit of information you forgot to mention?

So region has to be allocated and one can't find the empty values.

SHOW me an example - it might be a quick tweak, it might not.

You could add data validation to the region field to force users to fill it in - is that what you mean?

So I have to change the formula again for something you could have told me a long time ago?

I'll have a think about it and see if I can find a quick fix, but as EVERYTHING depends on the region being filled in, it might mean starting again (again).

Not happy that you have failed to flag this until now.

In A30:

Please Login or Register  to view this content.

That's your lot now.

By the way, if your regions are not in the format REG-nn, then this will fall over. I hope that's not going to be the next revelation ...

I've now got two versions.

For REG-nn formatted regions:

Please Login or Register  to view this content.

For named regions:

Please Login or Register  to view this content.

Take your pick.

It's your responsibility to think your queries through before you post and present them as fully as you can. What's slightly galling is having requirements added on after the fact that significantly alter the way the solution needs to be written. I've done probably three times as much work on this thread as I should have had to do if you'd presented all facts and eventualities at the start. See it as a learning process and think it through fully next time. Don't forget that nobody here is being paid for this, either by you or anyone else, so your getting help relies on our goodwill: that goodwill can erode quite quickly if someone is in the habit of drip-feeding requirements.

For each query, think:

1. How exactly does this need to work?
2. Does my sample data really reflect the reality of my data?

If you do this before you post, you are likely to get your issues solved much more quickly.

If you need any of the formulae explaining, just shout.

I am glad that it works for you, finally.

Thanks for the solution and advice for future.

Before using this forum, I always search Youtube and other forums and get solutions of my problem. The problem which I first posted before this thread was about repeated sequential numbers based on the value of other columns which you have provided instantly. When I am expanding my worksheet, the new problem arises, and I initiated this thread and where I found new formulae (Let, VStack, HStack, and LAMDA) about which i have no idea and never used before. That's why my non-stop queries emerged. Anyhow, your untired help is remarkable.

Thanks.

As I said, if you want an explanation, just ask (but it won't be today, as I'm out all day).