In this workbook, the worksheet named Bookings shows flight booking data as per the company’s records. The Worksheet named Vendor data shows bookings as per the vendor’s records. There are discrepancies at three levels between these two sheets. 1. There could be PNR’s in the company records (column B of Bookings) which are not present […]
Read More →
Assume a worksheet with formulas referring to the previous worksheet. When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet. In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by […]
Read More →
When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell. Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell. Furthermore, when splitting data into multiple cells, it should […]
Read More →
Assume a base data sheet with information for various vendors. There is also a worksheets which has the template of the reconciliation statement which is sent to all vendors. At the click of a button, one may want vendor worksheets to be created from the template. Furthermore, one may want to do either of the […]
Read More →
{ 34 Comments }
In range G5:G44, some numbers are entered. These numbers are custom formatted with General” Dr” for debit entries and General” Cr” for credit entries. One may want to extract the Dr and Cr in range H5:H44. The following process will get the desired result: 1. Press Ctrl+F3 > New and in the Name box, type […]
Read More →
{ 22 Comments }
Assume a two column database on a sheet named Demerge. There are duplicate values appearing in column A of this database. One may want to transfer all records of each unique entry in column A to as many different workbooks are there are unique entries in column A. So if there are six unique values […]
Read More →
{ 5 Comments }
Suppose a worksheet name is Code_details. The following values lie in range A2:A14 of this sheet: A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123, ASDFG1234567890 and ASDFG123456789 Suppose there is another worksheet named Summary where the following values lie in range B5:B7: ASQ47809876,ASW675458 and QWERT Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet. If this exact value is not found there, then the […]
Read More →
{ 5 Comments }
Assume a two column database in range B3:C11 of sheet1 of Book1.xlsx. Headings are in B3:C3. B4:B11 has names and C4:C11 has numbers. Save this file on the desktop. Open another workbook (Book2.xlsx) and type any name in cell B6 of sheet1 (the name should be one of the names mentioned in range B4:B11 of sheet1 of Book1.xlsx). Save this file […]
Read More →
{ 8 Comments }
Assume a two column database in range A3:B6. Headings are in A2:B2. In range A3:A6 are numbers, say 60,30,20 and 10. In range B3:B6 are some names, say Ashish, Sameer, Gautam and Sanjay. In cell A7, enter any name, say Sameer. The task is to extract the corresponding number from the range (A3:B6) into cell B7. […]
Read More →
{ 228 Comments }
Assume a folder named Test on the desktop. In this Test folder, there is another folder named Survey reports. In the Survey report folder, there are multiple Excel files. All the Excel files have the same structure i.e. if there is an Operator Names in cell C9 of one Excel file, then in other Excel files as […]
Read More →
Compare two sheets and prepare a Discrepancy Report
{ 4 Comments }