Solved

Way to Auto populate a column index

Posted on 2013-05-30
2
239 Views
Last Modified: 2016-11-23
I'm trying to figure out a way to have a button where it takes a column and with the information automatically add a ampersand and a unique number down a column (example:
A2 = BING&1
A3 = BING&2
A4 = DELL&3
A5 = IBM&4

So the individual would just enter BING, DELL, OR IBM in column A and after a button is pressed it gives it an ampersand and a unique number. I tried to do a simple IF statement telling to do a concatenate of column A an Ampersand and then a unique number in another column if there is a value in column A. But of course I get a circular reference.
IdentifyingColumn.xlsm
0
Comment
Question by:Southern_Gentleman
2 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39208666
Hi,

You will need a temporary column, say, [F], that will hold the value of the corresponding value from column [A] &, the row number.

For example:
F1=A1&ROW()

However, in your workbook, the data starts on row 2, so:

F2=A2&ROW()

Then cell [F1] (or [F2]) would be copied down column [F] to the extent of the data in column [A].

The contents of column [F] would then be copied over the top of column [A] as values.

Column [F] would then be cleared or deleted.

Do you need help with such steps (in Microsoft Visual Basic for Applications code)?

BFN,

fp.
0
 

Author Closing Comment

by:Southern_Gentleman
ID: 39209763
Thanks BFN, I actually went ahead and did it the way of VBA.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question