Data Manipulation

Jwgnwa

New Member
Joined
May 20, 2019
Messages
8
Hi Folks! I am back asking for your help. I receive a file in the following format. The rows that start with data in Column "A" are part numbers. Below those part numbers with data beginning starting in column "C" are accessory codes and accessory descriptions, pertaining to the part above. I am needing to have the accessory codes only appended to the part data row and moved to column"J", "K", "L", and so on depending on the number of accessories, which is variable according to the part order. The number of accessories could be between 0 and 25 per part. The number of parts could be in the thousands and varies with each order. Finally, I am needing the rows with the accessory codes to be deleted from the worksheet, so that I would end up with a would end up with only 5 rows of part information (Excluding headers) in the example below. Please scroll to the bottom for an example of what we need it to look like after manipulation, so that it can be imported into QuickBooks.

I worked on this for a couple of weeks trying various options, but the logic of getting this done beat my limited VBA knowledge. I appreciate your help.

ABCDEFGHI
1MfgCatPart NumberPart DescriptionAlias 1Alias 2QtyPurchaseSell
2HONHCGH10584L10500 Series Single Pedestal Desk Left 66W x 30DJohn's Office2nd Floor1402.48503.1
3$(L1STD)Grd L1 Standard Laminates
4.HLAM: Bourbon Cherry
5HLAM: Bourbon Cherry
6HONHCGH10515R10500 Series Right Return 29-1/2H x 48W x 24DJohn's Office2nd Floor1324.09405.11
7$(L1STD)Grd L1 Standard Laminates
8.HLAM: Bourbon Cherry
9HLAM: Bourbon Cherry
10HONHSNHIWMMIgnition 2 Task Mid-back, ilira backJohn's Office2nd Floor1292.11365.14
11.Y2Advanced Synchro- Tilt
12.AArm: Height and Width Adj. Arm
13.HHard Caster
14.IMMesh: Black
15($3)GRADE: III UPHOLSTERY
16.SXSilvertex
1723COLOR: Carbon
18.ALAdjustable Lumbar
19.SBBase: Standard Base
20.TFrame: Black
21ALSAOPP16536TPOptimize Tckble Pnl 65H x 36W w/out Top CapBucky's Office1st Floor4201.24251.55
22ALSAOPP16524TPOptimize Tckble Pnl 65H x 24W w/out Top CapBucky's Office1st Floor2158.34197.93

<tbody>
</tbody>
GIZA SIF DRAWING CSV(436)

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1MfgCatPart NumberPart DescriptionAlias 1Alias 2QtyPurchaseSellA1A2A3A4A5A6A7A8A9A10A11A12A13A14A15A16A17A18A19A20A21A22A23A24A25
2HONHCGH10584L10500 Series Single Pedestal Desk Left 66W x 30DJohn's Office2nd Floor1402.48503.1$(L1STD).HH
3HONHCGH10515R10500 Series Right Return 29-1/2H x 48W x 24DJohn's Office2nd Floor1324.09405.11$(L1STD).HH
4HONHSNHIWMMIgnition 2 Task Mid-back, ilira backJohn's Office2nd Floor1292.11365.14.Y2.A.H.IM($3).SX23.AL.SB.T
5ALSAOPP16536TPOptimize Tckble Pnl 65H x 36W w/out Top CapBucky's Office1st Floor4201.24251.55
6ALSAOPP16524TPOptimize Tckble Pnl 65H x 24W w/out Top CapBucky's Office1st Floor2158.34197.93

<tbody>
</tbody>
GIZA SIF DRAWING CSV(436)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Code:
Sub Jwgnwa()
   Dim Rng As Range
   
   With Sheets("Sheet1")
      For Each Rng In .Range("A2", .Range("C" & Rows.Count).End(xlUp).Offset(, -2)).SpecialCells(xlBlanks).Areas
         Rng.Offset(-1, 9).Resize(1, Rng.Count).Value = Application.Transpose(Rng.Offset(, 2).Value)
      Next Rng
      .Range("A2", .Range("C" & Rows.Count).End(xlUp).Offset(, -2)).SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top