Merge two array in one to fill column stacked chart

Question

What is the best way to merge those both array :

Array1
Red
Orange
Red
Red
Orange

Array2
3
7
9
15
32

The output excepted is :

Orange	Red
7	3
32	9
0	15

The Idea is to use this output array to do a stacked bar chart.

I've no Idea of how to do so. I thought about using loop in order to browse array 1 and 2 and then store in a new array values. But this seems quite time consuming. So what would be the most efficient way to do this ?

What I currently did is :

Set cht = output.ChartObjects("Chart 3").Chart
    With cht
        .ChartArea.ClearContents
        .ChartType = xl3DColumnStacked
         xdata = Array1
         ydata = Array2
            .SeriesCollection.NewSeries
            .SeriesCollection(1).XValues = xdata
            .SeriesCollection(1).Values = ydata
            .Axes(xlCategory).TickLabelSpacing = 1
    End With

Currently Array1 and Array2 are filled by this sample of code :

If (dbRecSet.RecordCount <> 0) Then
    Do While Not dbRecSet.EOF
        If dbRecSet.Fields(0).Value <> "" Then
            ReDim Preserve Array1(cpt)
            ReDim Preserve Array2(cpt)
            Array1(cpt) = Replace(dbRecSet.Fields(0).Value, " ", Chr(13))
            Array2(cpt) = dbRecSet.Fields(1).Value
            cpt = cpt + 1
        End If
            dbRecSet.MoveNext
        Loop
End If

The recordset looks like :

Color	Value
Red	3
Orange	7
Red	9
Red	15
Orange	32

but it does not work as excepted so I think that :

xdata = Array1
ydata = Array2

Is the wrong thing there. So I think to make this work I have to merge my 2 arrays in one to do my stacked bar chart.

But I am not sure of this hypothesis since it seems possible to do stacked column chart with two arrays on this topic

OK. So would have thought not too difficult to extract unique values of array 1, loop through each item and populate a 2d third array with the value from array 1 and value from array 2. — SJR, Nov 25 '21 at 16:16
@SJR did you [look this](https://stackoverflow.com/a/52988853/11167163) ? It seems possible to build Stacked column chart with 1 Array with Values and 1 Array With Name. But In my case it does not stack values when name are the same. — TourEiffel, Nov 25 '21 at 16:18
I would make the question about merging arrays and then another about the chart aspect. — SJR, Nov 25 '21 at 16:21
Well I think how ever you do it, you will need to create arrays, at least judging by that link. But I do not use VBA with charts so probably not the best person to advise on that. — SJR, Nov 25 '21 at 16:25
You can probably find the answer here: https://excelmacromastery.com/vba-dictionary/ — Brett, Nov 25 '21 at 16:55

score 0 · Answer 1 · answered Jun 22 '23 at 17:50

Assuming you have Office 365 compatibility, you can use a simple filter function to achieve the desired ‘merge’ (then apply the relevant part of your code as desired).

=FILTER($E2:$E6,($D2:$D6=A1))

[ sample screenshot

This function can be copied / applied under each column header.

If you are looking for a way to capture the col headers dynamically, then you could use something like this (which can be adapted for the ranges in 1st function ):

=TRANSPOSE(UNIQUE(D2:OFFSET(D2,MAX(FILTER(SEQUENCE(ROWS(D:D)),(D:D<>"")))-2,0)))

Unfortunately, filter does not lend itself to array / ‘spill’ outputs (if it could then you could have combined dynamic column headers and filtered matrix into a single harmonious ‘vstack’ function ).

Merge two array in one to fill column stacked chart

1 Answers1