Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Convert single digit to a range

0

I have age data in Excel as 18,19,20, and so on. I need to write a macro to convert this data as ranges i.e. 18-24, 25-34 and 35+. 

I can write If formula in excel for this - But need a macro as I get data like this everyday in 10000 plus rows and I need to convert it to ranges. With my limited knowledge on Macros I am unable to write a macro for the same. 

Can you help?

Answer
Discuss

Answers

0

Since you aren't fluent with VBA I suggest to give preference to a worksheet function which you will be able to maintain yourself. Here is one that does what you want.

=INDEX({"18-24","25-34","35+"},MATCH($A2,{0,25,35},1))

It refers to cell $A2. You can change the column. As you copy the formula down, the row number will change. So, make sure that you paste the formula in row 2 to begin with.

INDEX{"18-24","25-34","35+"} specifies your results, 3 text strings, each embraced by quotation marks, separated from each other by commas, and the group between curly braces. You can modify the strings. You can also add text strings to have more ranges.

MATCH($A2,{0,25,35},1) looks for the age in A2 in the sequence {0,25,35}. It looks for a match which is larger or equal and return the position of that number in the series. Any number below 25 will therefore return 1, 25 or larger will return 2. To better understand, paste this formula in a blank column of your own test sheet.

=MATCH($A2,{0,25,35},1)

The function will return the nth item from the range specified for INDEX, where n is the value provided by MATCH. If you add a range to INDEX{"18-24","25-34","35+"} you must add its specs in MATCH because if MATCH returns a number larger than elements available in INDEX an error will result. Three result ranges require three MATCH ranges. That's the reason for the 0 in {0,25,35}. It specifies the range from 0 to 24 (where 24 is smaller than the next number in the series). It follows that the numbers must be sorted in ascending order.

You may further like to google for "INDEX/MATCH" and learn more. So, you will be in full control of this function.

Since you have tens of thousands of rows each day you need a quick way of copying the function. Excel supports this. In case you didn't know, here is the method to deploy the formula from M2 to M2:M15000.

  1. Enter the formula in row 2 of any column (in my example M2).
  2. Modify the referenced column, if necessary, to point at the age column in your worksheet.
  3. Select the cell with the formula and press Ctl+C (or press the Copy button on the Ribbon's Home tab).
  4. On the Ribbon's Home tab click on the Find & Select menu button.
  5. In the dialog box that opens select GoTo ...
  6. In the Reference field of the dialog box that opens enter the address of the range you wish to paste to (in my example M3:M15000)
  7. Press OK. The specified range will be selected.
  8. Press Enter. The formula will be copied to the selected range.
Discuss

Discussion

Thanks for the response. I am going to try this and revert on how this works. 
Reshma Jan 3, '20 at 11:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login