Share via

Excel VBA - need to rollup sub products onto a single product line for editing

Anonymous
2021-07-15T03:25:49+00:00

Hi All

VBA novice. Need to roll-up product lines to the SKU level. E.g. I've got:

Item_ID Distribution Type SKU number
HELPER ROW
5327978 DC 673386
5327978 DSD 673386

And I want:

Item_ID Distribution Type SKU number
5327978 DC DSD 673386

I've coded a blank helper row inserted every time the SKU number changes. For rows 3 and 4 I want the info copied in the helper row if it is the same and concatenated. So above in the HELPER ROW the info in the Distribution Type col is concatenated. And there's another couple of columns I need to concat.

There's plenty of posts on concatenating a range but unfortunately I haven't understood them.

I've tried:

using a concatenate formula with cell offsets to select the next 2 rows below the helper row. (not a perfect soln as some products might have 3 variations)

And

using the built in & concatenator and looping through.

I think its a common need my awful code would just confuse but happy to post that if it helps.

Thanks

k

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-07-16T00:07:23+00:00

    Thanks Shane - appreciate the help. I'd forgotten about text join. Yes I'm a novice at VBA but I'm enjoying the challenge :-)

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2021-07-15T23:21:29+00:00

    Hi,

    This can be done quite easily in the Query Editor. If you are interested in that solution, share some data.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-07-15T20:40:39+00:00

    I'm sure there are some very good programmers on this site who can give you great code. I might be able to come up with something, but it wouldn;t be the best and it would take me a quite while. Hopefully someone else will step in to help you.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-07-15T03:49:38+00:00

    I know I'm doing this the wrong way but it keeps it close to how I might do it in excel which helps for a novice. Text is showing indented in the post editor - hope it stays that way.

    Sub ConCatToSKU()

    'Note for testing - delete any rows past 20

    Dim LastRow As Integer

    Dim CurrentRow As Integer

    LastRow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count

    CurrentRow = 2

    Range("A:A").NumberFormat = "General"

    Do While CurrentRow <= LastRow 

        ' Writes a flag in an unused cell I can use as an IF 

    If IsEmpty(Range("E" & CurrentRow).Value) = True Then 

            Range("DA" & CurrentRow).Value = "ZZZZ" 

                If Range("DA" & CurrentRow).Value = "ZZZZ" Then 

                    'I was just testing writing a formula into the helper row 

                    Range("E" & CurrentRow).Formula = "=Concat(E3,E5)"

    'I NEED TO HAVE THIS CONCAT FORMULA RUN FOR ALL THE UNIQUE VALUES IN COL E 

                    End If 

    End If

    CurrentRow = CurrentRow + 1

    Loop

    End Sub

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-07-15T03:36:50+00:00

    This may help: suppose the distribution types are in B2:B3 the following spreadsheet formula will concatenate them:

    =TEXTJOIN(" ",,B2:B3)

    this would produce a result of DC DSD.

    If you want help with coding, we would need to see what you have.

    Was this answer helpful?

    0 comments