I need to determine where the 1's are in a column of data and then identify or concatenate their rows in a cell.
For instance column A looks like the following:
Col A
0
1
0
0
0
1
0
In cell B1 I need to know in string format that the 1's are in rows "2 and 7". This will be part of an error message to alert the user that these rows need fixing.
Thanks for your help.
Hmmm...Why not 'conditionally format' column A to highlight cells (in red perhaps?!) that carry the value '1'
http://www.cpearson.com/excel/cformatting.htm
Life's a spreadsheet, Excel!
Say thanks, Click *
The data is in a sheet that is hidden from the user. I then have a sheet that provides test results of certain data entries on numerous sheets. This will tell them what sheet contains the error.
Does this mean you want the relative position numbers of the 1's within the data array?
For example, if the actual range address is A22:A28 the 1's are still located in positions 2 and 7, correct?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Yes, that's correct.
OK, if you want the results to appear in a single cell then you'll need some form of VBA to do this.
Copy the code below and paste it into a general module.
Assuming your data is in the range A2:A8, enter this array formula** in B2:
Please Login or Register to view this content.
=SUBSTITUTE(TRIM(aconcat(IF(A2:A8=1," "&ROW(A2:A8)-ROW(A2)+1,"")))," ",", ")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
This works perfectly! I was able to change it a bit to fit my application. Thanks
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks