ClioSport.net

Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • When you purchase through links on our site, we may earn an affiliate commission. Read more here.

Excel help - ideally before I resort to Hulk Smash and murder...



Gally

Formerly Mashed up egg in a cup
ClioSport Club Member
I've never felt more unintelligent than I did when I opened this thread. Absoloute mind boGgling s**t, my partner is an excel genuis and I have zero clue, ZERO!

Well done anyone that can decipher that code!
 

Darren S

ClioSport Club Member
I've never felt more unintelligent than I did when I opened this thread. Absoloute mind boGgling s**t, my partner is an excel genuis and I have zero clue, ZERO!

Well done anyone that can decipher that code!
I think the issue I have with it, is that I look at Excel being a genius and that it should just know that I want the number of days between Date A and Date B. No convoluted methods or arcane calculations - just a simple number.

Then it gets to be an arse and tells me the result is 08/03/1900.

Seriously Excel. WTF are you doing? I swear it's more stupid than ever compared to when I used Excel 2003 and similar, quite often.
 

Darren S

ClioSport Club Member
Where's Clippy when you need him?! :p

So - just created a new sheet to test if the formatting of the cells was important.

1632914390407.png


Formatted column A to be plain Text. Formatted Column C to Date format. As expected, cell A4 doesn't even attempt retrieve an answer and just sits there like a spanner. Cell C4 on the other hand appears to consult some mystic Precambrian calendar, removing 6 days, 6 months and 121 years for the displayed figure.

I assume that my acolyte levels of wizardry will now guide me to outputting some relevant number? I'm waaaay overthinking this - but Excel itself does not help in the slightest.
 

The Psychedelic Socialist

ClioSport Club Member
Where's Clippy when you need him?! :p

So - just created a new sheet to test if the formatting of the cells was important.

View attachment 1559850

Formatted column A to be plain Text. Formatted Column C to Date format. As expected, cell A4 doesn't even attempt retrieve an answer and just sits there like a spanner. Cell C4 on the other hand appears to consult some mystic Precambrian calendar, removing 6 days, 6 months and 121 years for the displayed figure.

I assume that my acolyte levels of wizardry will now guide me to outputting some relevant number? I'm waaaay overthinking this - but Excel itself does not help in the slightest.
Don't format stuff to 'Text', you almost never want that. Just leave it as general and then the formula will work.

If you format C4 as general too, it'll show correctly.
 

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
Don't format stuff to 'Text', you almost never want that. Just leave it as general and then the formula will work.

If you format C4 as general too, it'll show correctly.
He may need to then refresh the comments of the cell.

Also, it looks like this data is being queried, so the data type may already be set (in his previous examples)
 

MarkCup

ClioSport Club Member
Yeah, formats, always double check your formats.

Get them wrong, don't realise you've got some set to text in error, and they can f*** you up real bad. I've spent many a half day banging my head against a brick wall because of format issues.
 

Darren S

ClioSport Club Member
Yeah, formats, always double check your formats.

Get them wrong, don't realise you've got some set to text in error, and they can f*** you up real bad. I've spent many a half day banging my head against a brick wall because of format issues.
Must have an issue with the import of data. If I use the today() formula on a clean sheet with General formatting - all is good.

When I try to use it on the sheet that I want to, I get the bloody year 1900 date.

So annoying.
 

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
Also, it looks like this data is being queried, so the data type may already be set (in his previous examples)
That's come as a shock 😂

Can this sheet (or is it already) one that can use macros?
 

Darren S

ClioSport Club Member
A simple count help, this time around - if possible please?

I might have already gone a step too far and got myself into a dead-end - but here goes.

I've got an export of circa 32,000 rows of sales orders that had at least one order line of our top selling product within it. I now want to quantify how often other product codes appear within this list of 32k rows - giving me a list of what's numerically popular alongside the top selling product.

When I've done a simple countif formula, the results have worked (kinda) - but not summed themselves up.

So, as seen below....


18OSB
3​
18OSB
2​
18OSB
1​
24/12/3BG
2​
24/12/3BG
1​
24/12/3FW
3​
24/12/3FW
2​
24/12/3FW
1​
50MK
121​
50MK
120​
50MK
119​
50MK
118​
50MK
117​
50MK
116​
50MK
115​
50MK
114​
50MK
113​


.... the product code is on the left, followed by the counted quantity on the right.

I'd love it to appear similar to this:-

180SB 3
24/12/3BG 2
24/12/3FW 3
50MK 121

....and so on.

I've seen plenty of examples where adding the name of the product into the formula works a treat - so within in it, somewhere it would state "50MK". The problem comes with the spreadsheet being 32,000 rows tall and it would take me an age to identify each and every product code in that column.

It could be that my brain is now fried come Friday afternoon and I'm just numb to Excel again :p
 


Top