Last 50 Posts on Chandoo.org

Chandoo on 2019-04-20 05:20:41
Think... is it really random?

omar on 2019-04-19 13:17:45
214 :D if we don't download the pbix it is just random :D

T? ?i?n on 2019-04-19 08:51:04
So, i can see, it's a simple and effective solution. ???

Greg on 2019-04-19 00:10:42
Nevermind.. I saw Chrayu's comment after I posted. Still don't understand Power BI, but that's a different issue lol

Greg on 2019-04-19 00:08:12
I've never used Power BI (apparently it's an add-on for Excel, but I have not bought or downloaded it.) This doesn't appear to do anything but make you scroll through dates and/or click on sections of the heat map. Can anyone tell me what this should be doing other than showing me different shades of red?

Chris on 2019-04-18 17:04:21
Thank you too! Happy Easter!

Luke M on 2019-04-18 12:53:04
Glad to see the tradition continue, albeit in a new way. :)

Chirayu on 2019-04-18 09:20:01
From 21-APR-2019 To 21-APR-2019 Easter Egg: Thank You for loving Chandoo.org

Sushil Kumar on 2019-04-18 07:50:59
Your solution to Challenge #1 This is wrong because your value of 26 unit after deduction Discount Amount is shown 975 ... Actual value show is 650 after deduction so your formula is wrong

Sushil Kumar on 2019-04-18 07:48:24
Your solution to Challenge #1 This is wrong because your value of 26 unit after deduction Discount Amount is shown 975 ... Actual value show is 650 after deduction so your formula is wrong

Sushil Kumar on 2019-04-18 07:47:01
This is wrong because your value of 26 unit after deduction Discount Amount is shown 975 ... Actual value show is 650 after deduction so your formula is wrong

Sushil Kumar on 2019-04-18 07:44:19
Challenge #1 Simple Answer =(C7*$D$5)-(C7*$D$5)*ArrayFormula(max((C7>$F$7:$F$12)*$G$8:$G$13))

Faizan on 2019-04-17 14:50:03
Hi, I tried with this formula and it is working fine (based on salary is paid exactly after 7 days IE 8th day only accumulating 200$). =IF(AND(WEEKDAY(B7)=7, SUM(C1:C7) > 200), "Payment Due " & SUM(C1:C7), IF(AND(WEEKDAY(B7) = 1, ISBLANK(E7)), SUM(C1:C7))) Regards, Faizan

Hui... on 2019-04-17 10:08:59
@Gareth Probably not easily But seeing as the merge is done using VBA you could use that to save the file with the appropriate name

Gareth on 2019-04-17 08:36:41
Is it possible to include a mailmerge field in the file name? e.g Document For [Jeff] Jeff being the name of the person the letter is being drafted for. So in my destination folder I can just generate as many as I want without fear of the file being over written.

Manoj on 2019-04-17 06:45:21
Great website to learn MS Excel online. I am not proficient in Excel & want to learn several things to use it for preparing reports and collecting data. Just bookmarked your site for future reference. Thank you & all the best,

Karthik on 2019-04-17 05:38:29
Just In Time! I was about to write to Microsoft today morning requesting to include "CONCATIFS()" function in next update - to club multiple match entries for given criteria(s).. Never knew this "TextJoin" function.. Thanks a lot..! Awesome as always @Chandoo! Ps: I started making my living by doing Microsoft Excel Training to corporates, part-time - and base for that gyan is mostly from chandoo.org! Hope u wont ask me any royalty and I refer your website for any queries :) ..

Katherine Carr on 2019-04-16 17:53:19
Fabulous tips! For many years, I have been using nested INDEX and MATCH (exact) functions to extract records (often with the conditional IF function) , usually using Named Columns, which I find a preferable method to the VLOOKUP and HLOOKUP functions (where column and row location can be dictated). The named ranges are important to me, so that the formulas are interpretable regarding intent. This new TEXTJOIN function for the recent versions of Excel will be very helpful.

Ankarath Rajan Pradeep on 2019-04-16 17:37:29
Hi Chandoo. I learnt vlookup from you a few years back and had been very happy using it, with all its drawbacks. It always helped, and where it couldn't, like looking to the left, I still managed by manipulating my data. But recently I stumbled upon DGET and life has changed since. It handles multiple criteria and fills up all gaps of vlookup. DGET is my new favourite now.

Brij Bhushan on 2019-04-16 12:33:56
Nice collection of article, so inspiring and easy to read, keep posting thank you for sharing Have a Nice Day!

Henry on 2019-04-15 23:18:07
Hi Chandroo This website will be my go-to for any Excel chart ideas! It's amazing, thanks Chandroo. Can I ask you, or anyone else, if they know of a way to make the relationship lines appear as curves, rather than straight lines? Thanks for any tips and advice. Regards Henry

Will Simmonds on 2019-04-15 19:41:33
Looking for a function that will match names to tracking numbers of customers from two lists and sort them. Is this possible?

sunny on 2019-04-15 07:20:53
How to remove formula without removing filter

P Murugan on 2019-04-14 14:59:57
Thanks Buddy I was struggling for more than an hour, your suggestion saved me time. Thank you very much

Ankit Kukreja on 2019-04-13 20:22:56
My problem was to find the list of South African countries placed in a column in 1000 rows in a format: austria;algeria;brazil.. I placed all the South African countries in a cell with a delimiter ";". If you have sort of similar problem then below code will work for you making slight adjustments based on your problem statement. Sub test() Dim cnt As Long Dim cn1 As Long Dim i As Long For i = 1 To 10 arr1 = Split(Cells(i, 1), ";") arr2 = Split(Range("D3"), ";") For cnt = LBound(arr1) To UBound(arr1) For cnt1 = LBound(arr2) To UBound(arr2) If CStr(arr1(cnt)) = CStr(arr2(cnt1)) Then Range("B" & i).Value = "Match" GoTo nextlevel End If Next cnt1 Next cnt nextlevel: Next i End Sub

Matthew on 2019-04-12 18:25:39
This is a nice technique but I was wondering if I wanted to use the data table for other calculations the line item does not line up with the ABC classification. Is my understanding correct that the graph ignores the relationship between a line item and its class and instead looks at cumulative totals/classification independently ?

M Khalid Awan on 2019-04-12 14:30:02
Great service

Mezbaur on 2019-04-11 13:30:21
I can't see full page from my mobile. It's not mobile responsive. Please check.

VIKAS SHANKAR BURTE on 2019-04-11 08:47:46
I am preparing one data file. I have employees PAN details with me but in XX format and at other side i have entire employee master as well. Dont understand how to vlookup and get the correct PAN details. Please help

Roshan on 2019-04-11 07:36:03
Hi, Thanks for this trick for concatenating in columns. How to add any delimiter then like "/" or comma? I have a sheet of 30+ columns. Using Transpose first for adding delimiter creates problem with ; error. and with your way, i can't add any delimiter. Please help