Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-18-2018, 09:57 AM
mpapreja mpapreja is offline Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Windows 10 Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Office 2016
Novice
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA
 
Join Date: May 2018
Posts: 9
mpapreja is on a distinguished road
Default Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA


Dear Experts
I am having vertical Bill of Materials (BOMs) of my 1000s of items in Columnar form. In need to convert it into rowwise format BOMs.



The source BOM and desired output is shown in the attached excel file.


Can somebody help my achieving the above desired output?


Thanks for your help in advance.

The above query also posted on following forum:-


https://social.technet.microsoft.com...ba?forum=excel
Attached Files
File Type: xlsx 1-BomTranspose Macro - Forum.xlsx (11.2 KB, 5 views)
Reply With Quote
  #2  
Old 05-20-2018, 01:01 AM
mpapreja mpapreja is offline Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Windows 10 Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Office 2016
Novice
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA
 
Join Date: May 2018
Posts: 9
mpapreja is on a distinguished road
Default

Dear Experts,

With respect to my previous post I have made a Macro as follows which is working fine:-

Sub TransposeBom()
Option Explicit
Dim SrcRng As Range 'Source Range
Dim TrgRng As Range 'Target Range
Dim i As Integer, SrcNoofRecords As Integer, r As Integer
Dim RowOffset As Integer, Columnoffset As Integer
Dim PrevParentCode As String, CurrParentCode As String
Dim PrevParentCellAdd As Range
Dim CurrParentCellAdd As Range
Dim PrevRow, PrevCol As Integer


RowOffset = 0
Columnoffset = 0
c = 0
i = 1
j = 0

With ActiveSheet
Set SrcRng = Application.InputBox("Select a Source Range from Where Datat is to be Copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & SrcRng.Address)
Set TrgRng = Application.InputBox("Select a Cell in which the data is to be copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & TrgRng.Address)

End With

r1 = SrcRng(1).Row - 1

c1 = SrcRng(1).Column - 1
PrevParentCode = SrcRng(1).Value
R2 = r1 + 1

MsgBox (" The rows " & SrcRng.Rows.Count & " and the Columns " & SrcRng.Columns.Count)



For Each CELL In SrcRng.Cells


TrgRng.Cells.Offset(RowOffset, Columnoffset).Value = CELL.Value


c = CELL.Column
If c = 9 Then
If SrcRng.Cells(r1, 1) = SrcRng.Cells(R2, 1) Then
Columnoffset = Columnoffset + 1
r1 = r1 + 1
R2 = R2 + 1
'
Else
Columnoffset = 0
RowOffset = RowOffset + 1
r1 = r1 + 1
R2 = R2 + 1
End If
Else
Columnoffset = Columnoffset + 1
End If



Next CELL
End Sub


My source data was as per attached image:-



I am getting the output as shown in attached image by executing the above Macro:-





the only addition in above program needed is avoiding the repetition of Parent Items highlighted in red in above image. Any sort of help in above regard will be great help to me. Thanks in advance.

best regards

Last edited by mpapreja; 05-20-2018 at 01:17 AM. Reason: image not attached
Reply With Quote
  #3  
Old 05-20-2018, 09:05 AM
NoSparks NoSparks is offline Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Windows 7 64bit Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 814
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Based on your original post and attached file.
You only need to use column B data as your source range.
Code:
Sub TransposeBom()
    Dim lr As Long, rng As Range, cel As Range
    Dim ChildNum As Integer
    Dim destSheet As Worksheet, writeRow As Long
    
With Sheets("Source or Input")
    lr = .Range("B" & Rows.Count).End(xlUp).Row
    Set rng = .Range("B2:B" & lr)
End With

Set destSheet = Sheets("Desired Output")
writeRow = 5   'to keep away from posted desired results

For Each cel In rng
    If cel.Value <> cel.Offset(-1).Value Then
        ChildNum = 1
    Else
        ChildNum = ChildNum + 1
    End If
    
    With destSheet
        If ChildNum = 1 Then
            writeRow = writeRow + 1
            .Cells(writeRow, 1).Resize(, 3).Value = cel.Resize(, 3).Value
            .Cells(writeRow, 4).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
        Else
            .Cells(writeRow, 4).Offset(, (ChildNum - 1) * 5).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
        End If
    End With
Next cel
End Sub
Attached Files
File Type: xlsm 1-BomTranspose Macro - Forum.xlsm (19.7 KB, 3 views)
Reply With Quote
  #4  
Old 05-21-2018, 04:14 AM
mpapreja mpapreja is offline Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Windows 10 Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Office 2016
Novice
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA
 
Join Date: May 2018
Posts: 9
mpapreja is on a distinguished road
Default

Wow! Thanks a great help to me!!

Thanks. But how to mark it as an answer
Reply With Quote
  #5  
Old 05-21-2018, 05:35 AM
NoSparks NoSparks is offline Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Windows 7 64bit Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 814
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Quote:
Originally Posted by mpapreja View Post
Thanks. But how to mark it as an answer

From the Thread Tools drop down, on the line above your first post, you select "Mark this thread as solved"

You should also go to your other posting of this question and put a link to here.
Reply With Quote
Reply

Tags
bill of materials, bom, transpose

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA BOM (bill of material) umairn Project 1 04-17-2017 02:11 PM
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Integrating Bill Of Materials (BOM) GRT45 Project 1 03-16-2017 10:26 PM
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Inserting material after Endnotes ClaireB Word 19 10-01-2015 07:54 PM
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Project 2013 reading material Steve. Project 3 05-17-2014 07:08 AM
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA Purging duplicated material in a document NepalKat#1 Word 1 09-03-2011 05:53 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft