Question 1 - Dynamically copy unique combination of cells from a database to a range and then analyse the data

 

In the attached workbook, you will see my data in range A12:F32 of worksheet 2.  As you will notice, the only unique field is the Unit ID.  What i want to do is add the quantities for every combination of Plant name, state, fuel type and mover type.  While i can easily sum up the quantities by using the Sum(if(( array formula, my main problem is to dynamically copy the unique combination of the 4 fields mentioned earlier to a seperate range and then sum the quantities.

 

From: Kanwaljeet

Workaround

 

Please find herein my workaroud to the problem.  My solution is in range A1:E9 of the workbook.  The sum of quantities is given in range E2:E9 of the workbook.  Also, i have not used an array formula as mentioened in your question because array formulas add to file bloat and adversely affect the speed of execution.

 

Hope this helps.

---

  Question 2 - Count unique items in a Pivot table

  Question 3 - Show minimum value in a pivot table

 Question 4 - Lookup a value in a range which may fall in one of many ranges

 Question 5 - Determine principal outstanding for loans of varying amounts, maturity and EMI's

We would want to arrive at the principal outstanding on basis the below 4 information available with us Loan amount, Tenor, EMI and EMI's paid.
 
Please find the page attahced where we have multiple entries of loans amounts taken by the customer and the details attached. I want the POS ( principal outstanding ) to get automatically filled up once i enter the other details . Am also attaching an amort schedule in sheet named "Amort schedule" of the same file for your reference.

From: Giridhar

Workaround

Please find herein my workaround to the problem.  As and end user, you just need to change the values in range C:F of sheet named "Loan Details".   Also, as discussed with you, you would be fine with dealing with just 50 loans - however, i have provided you the flexibility to work with upto 58 loans.  To cross check the values which appear in the first sheet, please plug in the loan, tenor and EMI in cell B1, B2 and B4 of the sheet named "Amort schedule". 

Also, as and end user, you would not need to bother about "Ashish solution (set 1)" and "Ashish solution (set 2)" - you may hide these sheets if you wish to.

Hope this helps.

 Question 6 - Filter specific data to an output sheet depending upon user selection

Depending upon a user drop down in the "Control" Sheet, i would like to transfer the output from the "Database" sheet to the "Final Output" sheet.

From: Pankaj Shitole

Workaround

Please find herein my workaround to the problem.  Please ignore the working in range A10:F26 and cell K10 of the "Control sheet".  As an end user, you just need to make a selection in the drop down box in the "Control" sheet and see the output in the "Final Output" sheet.  Also, please ensure that the entries in the "Category" column of the "Database" sheet appear in the range A10:A26 of the "Control" sheet.  Therefore, once you have the final category names in column A of the "Control" sheet, please ensure the following:

1. The formulas in column F are extended till that row:

2. Right click on the drop down box and go to format control.  On the Control tab, ensure that the input range has the reference of all the entries in column A.

Hope this helps.

 Question 7 - Split data into tabs (department wise)

The question here is to create a seperate tab for each department such that the output is dynamic for the following:

1.  Changes in departments i.e. if the department changes, then the information should appear in the appropriate tab;
2.  New data added i.e. for new data added (below row 17), the information should appear in the appropriate tab.

From: Excel forums of www.merawindows.com

Workaround

Please find herein my workaround to the problem.  The instructions are mentioend in the file itself.

Hope this helps.

 Question 8 - Return unique values from a range of duplicates

In the attached file (please click on "solutions" below), on the "Data" sheet, there are 5 columns of data with data being repeated in the first and second column.  On the solutions sheet, i enter "Atlanta" in A6 and "Retail" in B6.  In column D, i would like to see the unique values from column C of the "Data" sheet where the following 2 conditions are satisfied:

1. The entry in A6 is found in column B of the "Data" sheet;

2. The entry in B6 is part of the strings mentioned in column C of the "Data" sheet

To complicate things, the entries in column C of the "Data" sheet contains duplicates and i want each title to be listed only once.

From: Excel forums of www.merawindows.com

Workaround

 Question 11 - Return rows/article numbers which have sequential sizes

In worksheet named "Question" of the attached file (please click on "workaround" below), i would like to filter those articles (to a seperate range) which are available in sequential sizes i.e. extract only those articles which have sequential sizes such as S,M,L or M,L.  If an article number has non-sequential sizes such as M,XL, then ignore that article number

From: Excel forums of www.merawindows.com

Workaround

 Question 13 - Validate row entries based on multiple conditions

I would like to enter data in rows based on multiple conditions.  The exact question can be found at the following link

From: Excel forums of www.merawindows.com

Workaround

 Question 14 - Apply Goal seek on multiple cells at once

I would like to apply apply goal seek on multiple cells at once instead of applying it individually on 150 cells.

 Question 15 - Perform calculations in a pivot table based upon input selected in a validation drop down

The question (and my workaround) is explained in the attached file.  Refer to sheet named "Base_data and question" for the question

From: Excel forums of www.merawindows.com

Workaround

Please find herein the workaround to the problem.  The instructions are mentioned in sheet named "Workaround - Steps".

 Question 16 - Apply advanced filter based on cell background colour

I would like to apply an advanced filter based on the cell background filter and transfer the relevant rows to another worksheet.  Furthermore, i should be able to select (in a cell drop down) the colour which i wish to apply a filter on

 Question 20 - Distribute value against a certain date to other values proportionately

Since the question is quite lengthy to explain, i would request you to read the question from the following link.