Assigning Supplier ID number to each transaction line

[universe2030]

Hello,

I am working on a macro that takes the data from my source worksheet (this includes a list of clients with unique supplier ID numbers, as well as dollar transaction amounts for multiple types of accounts) and organizes those transactions in a new worksheet by supplier ID, transaction number, account type and dollar amount.

Here is a sample of my source worksheet:

I want to organize the data in a new worksheet with the following format:

I am having trouble figuring out how to associate the supplier IDs with each transaction, as well as getting the transaction # to reset back to 1 at the next supplier ID.

Any help will be greatly appreciated. Thank you.

 

[nemmi69]

Try this

Option Explicit
Dim SupplerID As Integer
Dim AccountID(3)
Dim TranCount As Integer
Dim LastRowNo As Long
Dim EXloop As Long
Dim Transloop As Integer
Dim TransCount As Integer
Dim RowCount As Integer


Sub ExtractInfo()
LastRowNo = ActiveSheet.Range("B1048576").End(xlUp).Row
If LastRowNo <= 3 Then Exit Sub
'get account IDs
For Transloop = 0 To 3
    AccountID(Transloop) = ActiveSheet.Range("C2").Offset(0, Transloop + 1).Value
Next Transloop
'this is on the same sheet
'starting at row 15
RowCount = 15
ActiveSheet.Range("B" & RowCount).Value = "Supplier ID"
ActiveSheet.Range("C" & RowCount).Value = "Transaction #"
ActiveSheet.Range("D" & RowCount).Value = "Account ID"
ActiveSheet.Range("E" & RowCount).Value = "Amount"
RowCount = RowCount + 1


For EXloop = 4 To LastRowNo
    SupplerID = ActiveSheet.Range("B" & EXloop).Value
    TransCount = 1
    For Transloop = 1 To 4
        If Trim(ActiveSheet.Range("B" & EXloop).Offset(0, Transloop + 1).Value) <> "-" Then
            ActiveSheet.Range("B" & RowCount).Value = SupplerID
            ActiveSheet.Range("C" & RowCount).Value = TransCount
            ActiveSheet.Range("D" & RowCount).Value = AccountID(Transloop - 1)
            ActiveSheet.Range("E" & RowCount).Value = ActiveSheet.Range("B" & EXloop).Offset(0, Transloop + 1).Value
            TransCount = TransCount + 1
            RowCount = RowCount + 1
        End If
    Next Transloop
Next EXloop


End Sub

 

[universe2030]

This is what I was looking for. Thank you very much.

One follow-up question. How would I get the transactions with $0 amounts to not show up at all? For example, if that first $0 transaction for supplier 1276 wasn't there and instead, the next one with the $1,200 amount was transaction #1 instead of #2 .

 

[nemmi69]

What exactly is in the cells with no value?

 

[nemmi69]

Depending on whether the cell is blank, zero or has a "-" the line

" If Trim(ActiveSheet.Range("B" & EXloop).Offset(0, Transloop + 1).Value) <> "-" Then"
will need editing

 

[universe2030]

The cells would have a 0 (such as the three highlighted in my last screenshot). I don't want them to show up as a transaction at all.

 

[nemmi69]

Change the line to
" If Trim(ActiveSheet.Range("B" & EXloop).Offset(0, Transloop + 1).Value) <> 0 Then"