Creating an array of numbers

  • Thread starter Thread starter rbrychckn
  • Start date Start date
R

rbrychckn

I have two columns of information that I'd like to use to create an
array. Column A tells me what sheet has a particular flag in it.
Column B tells me how many flags are on that sheet.

A B
0 0
2 1
0 0
4 1
0 0
0 0
7 3
0 0


I'd like to put in C a set of arrays that if A<>0, then create an array
of numbers, one for each entry. The result I'm looking for would look
like the below.

A B C
0 0
2 1 {21}
0 0
4 1 {41}
0 0
0 0
7 3 {71,72,73}
0 0


Ultimately, I want to use this using an OFFSET(INDIRECT(SMALL()))
function to list out all of my flags on one sheet. Any help is
appreciated.
 
Not sure how big your values get, put for small values in B use
=IF(A1<1,"","{"&A1)&IF(B1=0,"",1)&IF(B1>1,", "&A1&2,"")&IF(B1>2,",
"&A1&3,"")&IF(A1>0," }","")
For larger values you will need VBA
Bernard
 
Back
Top