Assume there are multiple files saved in a specific folder. There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder. Furthermore, Excel files can have following extensions – .xls,.xlsx.xlsm. Each Excel file has multiple worksheets with one worksheet being named Sheet1. While data on sheet1 of all Excel files will start […]
Read More →
{ 0 Comments }
Assume there are numbers typed in range B3:G8. In row 10, one may want to sum up diagonal cells i.e. in cell B10, one would want to get the value from cell B8, in cell C10, one may want to sum values from cells B7 and C8. in cell D10, one may want to sum […]
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 →
{ 72 Comments }
Assume there is data in range A2:BG2185 of a worksheet (Range A1:O1 have headings). In column A are years and in column B are Countries. Other columns have some text/numeric data. In column A are years ranging from 1984 to 2009 for each country mentioned in column B. Therefore, Albania would appear in range B2:B27 and 1984-2009 […]
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 →
{ 4 Comments }
Assume numbers in range C10:C13 as 12,23,34,45. Assume numbers in range E10:E13 as 56,67,78,89. For uploading these two columns (along with other columns as well) into an ERP, C10:C13 and E10:E13 need to be converted to text values i.e. numbers stored as text values. Once way to do this is to: 1. Select C10:C13 and to go Data […]
Read More →
Assume there are two types of inverters – Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW. For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used. Therefore the number of inverters which can be used for a load factor of 60kW […]
Read More →
Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook
{ 8 Comments }