• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Summary using excel formula.


Dear Sir,
I wanted to put Customer name in front of Invoice number in Column A where now its serial number.
Yellow highlighted is the example how my sheet should look like.
Please advise what would be the best solution for the same.
And date in DD-MM-YYYY format.
Please advice.



Well-Known Member
Select Column A
Press F5 on the keyboard
Click Special…
Choose the Constants radio button and the Numbers checkbox like:

Click OK.
Without disturbing what's selected, type at the keyboard = then the up arrow on the keyboard (the cursor control keys) then hold the Ctrl button down while you press Enter on the keyboard.
Select Column A again, then copy, paste-special|values to convert the formulae to plain values.

The dates in your sample file are mostly strings in the day/month/year order.
Select the single column of dates, then using Text to Columns in the Sort & Filter section of the Data tab of the ribbon, choose Delimited, click Next, make sure all the Delimiters checkboxes are empty, click Next, choose DMY:
DMY is important because that is the arrangement of the ORIGINAL data. Later you'll be able to display the dates in any format.
Click Finish.
Now format the cells with dates in to display the dates as you want.