solutiontogo

www.solutiontogo.de

[solutiontogo-blog]

Why this is the Golden Age ... and what the IT Revolution 's got to do with it!

[solutiontogo-blog]

[solutiontogo-blog]

We’re a specialized provider of software development services with a very strong track record especially in Finance, sales and administration. We provide customised solutions that cater to specific business needs involving Microsoft Office Applications . We develop simple and elegant solutions such as Automation and Performance Optimisation, Quality Management & Functional Redesign and New functionalities for existing applications as well as new applications.

[solutiontogo-blog]

In-cell drop-down lists in Excel

It’s about user comfort and data quality: Using in-cell drop down lists prevents wrong manual data entry and enhances substantially usability of entry masks or reportings. Here’s a quick guide on how to implement this!

I think you got it: if you pick a value from a list of options from a drop-down box, this is known as in-cell drop-down lists.And what about conditional in-cell drop-down lists? Well, this is where the drop-down list varies depending on the selection in another drop-down list. We’ll show you how to implement this.

Consider the scenario where we have a list of States (I decided to pick the top 3 US states in the alphabetic list : Alabama, Alaska and Arizona), a list of 3 cities in each of these states, and a list of 10 zip codes in each of these cities.

Columns B, C, and D contain drop-down lists. If you select Arizona in the drop-down list in column B, only cities in Arizona will be displayed in the drop-down list in Column C. If you select Alaska, only cities in Alaska will be displayed in the drop-down list in Column C. Similarly, when you select a city in Column C, only the zip codes of that city will be displayed in Column D. Once a zip code is selected, the corresponding population of that zip code is displayed in Column E.

To create conditional lists, complete the following procedures.

Enter the data

1.First, we’ll create the table as shown above, in columns B,C,D and E with only the Column titles in Row 2 and no data.2.Next we will create some lists with names (named ranges).a. First create a vertical list of states :

In Cell G1, type State.

In Cell G2, type Alabama.

In Cell G3, type Alaska.

In Cell G4, type Arizona.

Select this range G1:G4, press CTRL+SHIFT+F3, a dialog box appears, click OK. This automatically creates a named range called State that has 3 values in the range (Alabama, Alaska and Arizona).

1.First, we’ll create the table as shown above, in columns B,C,D and E with only the Column titles in Row 2 and no data.2.Next we will create some lists with names (named ranges).b. Next we will creates horizontal lists of cities for each state:

In Cell H2, I2, J2, type Huntsville, Birmingham and Montgomery respectively (cities in Alabama).

In Cell H3, I3, J3, type Fairbanks, Anchorage and Juneau respectively (cities in Alaska).

In Cell H4, I4, J4, type Tucson, Phoenix and Mesa respectively (cities in Arizona).

Select the cities in Alabama in the range G2:J2, press CTRL+SHIFT+F3, a dialog box appears, click OK after selecting Left Column checkbox since it is a horizontal list. This automatically creates a named range called Alabama that has 3 values in the range (Huntsville, Birmingham and Montgomery). Repeat this for remaining states.

c. Now, we’ll create vertical lists of zip codes for each city from Columns G to O. Some cities have fewer zip codes due to lower population. Create named ranges for all cities automatically by selecting the entire range and pressing CTRL+SHIFT+F3 and clicking OK.3.After creating the lists and defining the names as in step 2, we are ready to create the drop-down lists.a. First we will make an in-cell drop-down list for State.

Select cell B3

Go to Data ->Data Tools group ->Data Validation

Fill the values in the dialog box as shown below and click OK.

This will create the in-cell drop-down list in B3. Drag the cell content downward from B3 to B7 (or how many ever rows you desire) to copy this to the remaining cells in column B to create the same drop-down list in all those cells.

b. Next we will make the first conditional in-cell drop-down list for City.

Select cell C3

Go to Data ->Data Tools group ->Data Validation

Fill the values in the dialog box as shown below and click OK. Note the use of INDIRECT function.

This will create the conditional in-cell drop-down list in C3. Drag the cell content downward from C3 to C7 to copy this to the remaining cells in column C to create the same drop-down list in all those cells. (Now if you choose a State in Column B, the City field in Column C displays only cities for that State.)

c. Repeat the above process with cell D3 to make the second conditional in-cell drop-down list for Zip code. This time the data validation dialog box should use the formula =INDIRECT($C3).d. We are done creating the drop-down lists.4.To make it more useful, I have added a last column Population that uses a VLOOKUP function to display the population for the selected Zip Code in Column D.Hope you enjoyed this tutorial on creating conditional in-cell drop-down lists.One last hint: If your data is dynamic (say, the number of cities keeps changing, and/or the number of states keeps changing based on an automatic import from a database) it’s also possible to have an automatic update of this named ranges. Please check on our Website for our offering regarding Excel macros.(www.solutiontogo.de)

[solutiontogo-blog]

Duplicate removal in Excel

Today’s IT landscapes are characterized by heterogeneous databases, quite a challenge for managing data quality. Quality checks must thus be a routine. One important method: Duplicate removal. Here’s how to get duplicate removal right in Excel.

Often you run into a situation where you want to identify the unique records in a worksheet and maybe delete the duplicates.

Method 1: Use Remove Duplicates icon (works only in Excel 2007 and above)

This method is irreversible! Please make a copy of your data first to another blank section of the worksheet.

1.Select any cell in your data set.2.Go to Data ->Remove Duplicates.3.From the Remove Duplicates dialog choose the columns which should be considered. For instance, if records where both the Sales Rep and Region were identical are to be removed, check the box for both fields. If you are only trying to get a unique list of Sales Reps, choose only the Sales Rep field.

4.Click OK.

It will report that n1 duplicates were removed and n2 records remain.

Method 2: Use Advanced Filter with the Unique Option

1.To the right of your data, in cell F1, copy the heading from the Sales Rep column where you want to find unique values.2.Select a cell in your data set.3.In Excel 97-2003,Go to Data ->Filter ->Advanced Filter. In Excel 2007 and above, go to Data ->Sort & Filter group ->Advancedicon.4.This will bring up the Advanced Filter dialog box.

5.You can choose one of two radio button options. Either Filter the list, in-place or Copy to another location.6.If you choose to Filter the list in-place, with the Unique records only checkbox checked,all duplicate records will be lost.7.If you choose Copy to another location, thenIn the Copy To box, specify the copy of your heading. In the Figure, this is cell F1.8.Click the box for Unique Records Only and click OK.

Excel will provide you a unique list of customers in column F.

Method 3: Use a Formula to Determine if a Record is Unique

The COUNTIF function can count how many records above the current record match the current record. To make this work, you need to use absolute addressing using the ‘$’ symbol. Copy this formula to the remaining cells. The formula in E9 will read: =COUNTIF(C$1:C8,C9)=0.

You can now sort column E and the unique values will be at the top or bottom of the list depending on the sort order.

Method 4: Use a Pivot Table to get Unique Customers

A pivot table is great at finding unique values. This is the fastest way.

1.Select a cell in your data set.2.In Excel 2000-2003, go to Data ->Pivot Table and Pivot Chart Report and click on Finish. In the Pivot Table Field List, click on the Customer Field. Click the Add To button.3.In Excel 2010 go to Insert ->Pivot Table-> Pivot Table. Select Region followed by Sales Rep as the Row Labels of the Pivot Table.

Excel will show you a unique list of Sales Reps by Region.

In many cases you may have specific requirements for duplicate removal, such as giving a list of removed duplicates in a separate list, or you may want to introduce certain exceptions or rather than removing duplicates you would want them only highlighted with a certain background colour. You may realize that a formula-based approach for duplicate removal applied to huge amounts of data won’t work efficiently. In these cases, a macro-based method can be implemented quickly and easily. Please check our microtools forExcel on our website www.solutiontogo.de. We like to call them microtools because they can be easily added to any application … and development takes only a few hours!

[solutiontogo-blog]

Formatting for Excel Calculations

It’s a major source of incorrect results: Numbers formatted as text. For example, mathematical functions won’t work anymore for instance, after importing data into Excel from sources such as the internet. Check out the VALUE function!

There are times, when you import data into Excel from other sources such as the internet or other applications, data types are inadvertently changed. Often, Excel stores numeric data as text which is not desirable: data in these columns cannot be used in mathematical or statistical functions such as SUM, AVERAGE, etc. and use of these functions results in errors. We will look at a method to convert the text to numbers using the VALUE function. Subsequent use of mathematical or statistical functions will result in the correct result.

Here is a quick tutorial on the use of the VALUE function to convert text to numbers.

Text or Number? -  A quick check

When a column in a worksheet holds text, by default the values are aligned to the left. If they are numbers, then the values are aligned to the right by default. This is a quick way to identify the data types.

In the image above, both columns are aligned on the left side because they are formatted as text. The column labeled Units should have been numeric data.

VALUE function

DESCRIPTION

Takes the value of a cell in text format and returns the value as a number, where Text is the data to be converted. This can either be a cell reference or the actual text in double quotes.

SYNTAX

EXAMPLE

1. Enter the data into Column B and C as shown above – as you enter each number in Column C, typea single quote ( ‘ ) in front of the valueand turns it into text. C2 – ‘123  C3- ‘456 etc.

2. Now type the formula =SUM(C2 : C12) into cell C13. Instead of the expected sum, you will get an answer of zero. This is because the values being added are text and thus ignored by the SUM function.

3. Click on cell D2 and type the formula =VALUE(C2). This function is one of the Text functions of Excel. The numeric equivalent of the cell value in C2 will appear in cell D2, and thus it should be right-aligned by default.

4. Copy the formula to the remaining cells in that column up to D12 by dragging on the fill handle.

5. Now type the formula =SUM(D2 : D12) into cell D13. Since the values being added are numbers, the correct answer will be displayed.

I hope that this tutorial provided a satisfactory answer to the question, as to how to quick-fix wrongly formatted numbers in Excel. However, from an IT point of view one question is even more intriguing: How come you have wrongly formatted numbers in your Excel spreadsheet in the first place? We strongly recommend thatyou take a closer look at the import process, that’s generally where this issue arises. You’ll find some useful advice on state-of-the-art import methods on our website in the section on Excel Reporting.(https://www.solutiontogo.de/)