Empty Row after every group of data

Patience, please - it hasn't even been 24 hours since you posted.

You can get them by signing up to the beta channel. They will eventually be released to all.

Kindly Be Patient. Free help has a price. You have to wait until someone can/wants to help you. Do not repeatedly bump your thread on the day you posted it. That is a bit demanding.

Anyway...

Of course it can be done. But the formula is a bit of a monster.

The beta channel is fine... until you send your sheet to someone using O365 who does not have the beta channel. It's probably better to wait!!

Formula:

Please Login or Register  to view this content.

Adjust the ranges in red, as needed. If you want a symbol displayed, instead of an X, replace the bits in cyan, se desired.

However, I was taken aback when I received a reply with an inappropriate tone from you and from @AliGW in a previous thread.

I can find no 'inappropriate tone' from me in any of your threads.

To avoid any misunderstandings, it would be beneficial for newly registered users to receive an email for email confirmation, followed by a warm greeting email that includes the Forums Rules and Expectations for both help seekers and providers.

When you joined the forum, you ticked a box saying that you agreed to our rules. There's a link to our rules page at the top of this page in the menu bar - the expectation is that anyone having agreed to our rules will read them to make sure they know what they are abiding by. Most people manage to do that. I suggest you go and read that page now.

Thank you for your comments. However, I feel that I am unable to help you further, entrely as a result of your comments in your last post.

Good luck, but I shall not be offering help to you in future.

So because you didn't get a solution to ONE of your queries, you have decided to leave? You cannot guarantee always to get a solution. Seems to me that you are cutting off your nose to spite your face. But it's your choice. Goodbye and good luck.

Yes,I see the problem. However, I will not be attempting to fix it. If I spend and hour or more on a problem, and the OP verbally gives me a kick... then I stop playing.

You have been shown no disrespect by anyone here. You were asked to be patient - that is all.

I am willing to remain if the help provider demonstrates respect towards the help seeker, ...

To be brutally honest, nobody will be concerned whether you choose to stay or go. Thousands of members are helped here - you are just one person taking umbrage at being asked, perfectly politely, to be patient. As I said, it's entirely your choice whether you stay or leave.

Please stop making wild and unfounded accusations.

I do not like getting things wrong, so as a challenge, I took another look. It took me about 5 minutes to fix it.

And until such time as you stop sounding like a self-entitled princess, I shall not post my amended file.

I am aware of this, which is why I have never taken the time to express gratitude to each person who has offered assistance.

???

instead of assuring them that none of us intended it and promising to be more cautious in the future,

Sorry, but I am not going to apologise for something that you have perceived, but that is unfounded. You have persistently failed to identify what you perceive to have been 'harsh', and instead made generalisations. You've offended the member who was helping you, and he has stood back.

I am closing the thread - it serves no further purpose. If you don't like the forum, then feel free to leave. If you wish to stay, then you will need to abide by our rules and guidelines. Your choice, but I am not going to take any further unwarranted criticism from you (or anybody).

Following an exchange of PMs with Mubijazz, and in consultation with AliGW, I am re-opening this thread.

This may not (yet) do exactly what you need, but I think I have addressed the issues which you raised... somewhere in the distant past!

If it's not exactly right, tell me where it breaks down.

Glenn - it starts out looking one way, but as soon as you hit ENTER, looks like this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H	I	J	K	L	M
2	Region	Employee	Pro-1	Pro-2	Pro-3	Pro-4
3	REG-1	Emp-1	x
4	REG-1	Emp-2		x
5	REG-1	Emp-3				x
6	REG-1	Emp-4		x
7	REG-1	Emp-5	x
8	REG-1	Emp-6		x
9	REG-2	Emp-1			x
10	REG-2	Emp-2		x
11	REG-2	Emp-3			x
12	REG-2	Emp-4		x
13	REG-3	Emp-1			x
14	REG-3	Emp-2		x
15	REG-3	Emp-3			x
16	REG-3	Emp-4		x
17	REG-4	Emp-1			x
18	REG-4	Emp-2	x
19	REG-4	Emp-3			x
20	REG-4	Emp-4		x
21	REG-4	Emp-5	x
22	REG-4	Emp-6			x
23	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
24	Grand Total		20.00%	40.00%	35.00%	5.00%

Sheet: Sheet1

How/where did you hit enter? It does not do that for me.

Just any interaction with the worksheet and the layout changes. I have the same version as you, though. If I close and reopen without saving, it’s fine again until I move the mouse.

I did not encounter AliGW's problem but adding to the table, the results are as shown.

As an aside, anyone offer explantion use to why (my) camera tool has black background: I can only get images by colouring cells!

Can you make it change, save it and post it... I definitiely do not see the same behaviour...

Can’t investigate further - just about to go out with the family to celebrate my big birthday (a couple of weeks early).

JT, I thought I had sorted that aspect of it. However, it should be an easy fix. Regarding your camera tool... no idea!

JT, that bug is now fixed.

@GK: looking very good - although the solution is way beyond my pay scale.

Re adding image attachments is there another way, other than camera tool, as Ali's post does not (to me) appear to be a camera tool image? Showing my ignorance here!

She uses some fancy BB code option thing from "manage attachments". I used to use something like it, but stopped, as I always prefer uploading (editable) attachments. If I want to upload a pic I use CTRL-Prt Sc and resize the image in Paint 3D, saving it as a .PNG.

There's still one bug in it... the REGs need to be pre-sorted. They were in the sample. If that's not the case in reality, it's a minor-ish problem, I will need to replace the two COUNTIF(s) with something that does not need a range to work.

Like photography, and shooting in RAW format... once you start you never shoot in JPG again. Once your learn to use LAMBDAs... Mind you, I still don't know when/how to use MAKEARRAY.

Originally Posted by josephteh

With PivotTable.

Back at Post 1, the OP ruled OUT the use of Pivot Tables...

Re LAMBDA and example below from GK formula

D,DROP(REDUCE("",C,LAMBDA(y,z,HSTACK(y,BYROW(B,LAMBDA(x,IF(x=z,"x","")))))),,1),

I find it difficult to "separate" the parameters vs calculation in the above: they almost seem recursive!

B , C and D are OK but x,y,z ?

OK, so. You've picked a difficult one to chew on. This is a nested lambda.

DROP(REDUCE("",C,LAMBDA(y,z,HSTACK(y,BYROW(B,LAMBDA(x,IF(x=z,"x","")))))),,1)

The innner lambda - the bit in red - is a BYROW funtion, where B is defined as X. It goes, in effect down an array, top to bottom. When writing it I would have had Prod-1 and not z. The outer black bit is a second lambda... needed to cover all the Prods (left to right) using REDUCE, which takes two parameters, and goes, in effect left to right across the array. It takes two parameters... "" and C (the list of Products), which I defined in the LAMBDA as being y and z.

I've been mucking around with these for a year or so... and it seems that when you want to apply a complex function to a 2D array, you have to do the top-to-bottom bit before doing the left-to-right bit. At least that's how I >>think<< it all hangs together...

Clear as mud???

Do you mean ... takes two parameters... "" and C rather than "" and B?

All far too much for my old brain ... makes VBA look so easy!

But I very much appreciate you taking the time to explain: one day the coin may drop.

Yes: Nothing and C REDUCE("",C,LAMBDA(y,z,..... Nothing =y, C=Z... and I suspect that I'm older than you (I'm a virtual fossil),

I'm a virtual fossil

.... likewise!

Originally Posted by Glenn Kennedy

JT, that bug is now fixed.

First is it mandatory to convert the range into Table. My whole data is in range and many functions like filter() in some columns will not work if I convert my data into Table.

How the values of G (6,4,4,4,2) can be used in parameter D where 'x' as a symbol is used? Can the values 1/6,1/4 be displayed instead of 'x'?

The issue is when I applied conditional formatting, the font style "Wingdings" is used for Char(252), it is also applied on sub-total row which is not fixed.

She uses some fancy BB code option thing from "manage attachments". I used to use something like it, but stopped, as I always prefer uploading (editable) attachments.

It's not via Manage Attachments and it's not a picture - it's BB code pasted directly to the post and it can be copied and pasted from there directly into Excel. Mostly I upload attachments except where I particularly want to illustrate something.

It's an Excel add-in, but I can no longer share it as its creator has removed the public link to it from the web.

The attachment to post #29 is now working as expected - it is no longer spontaneously changing format.

1. I will look at this tomorrow.

2. Even an interim "thank you" might be nice. You neither own me nor pay me.

3. I do not understand what you mean by your last two comments. Please illustrate more clearly. Mock up what you expect to see.

On second thoughts... forget it. I am OUT. You clearly have learned NOTHING from today's exchanges.

Good luck and good bye.

Originally Posted by AliGW

The attachment to post #29 is now working as expected - it is no longer spontaneously changing format.

Seems I was wrong - see attached.

See earlier posts and PMs to you.

"Frankly my dear, I don't give a damn".

Rhett Butler, 1939.

That's entirely your prerogative.