Data Manipulation

Jwgnwa

New Member
Joined
May 20, 2019
Messages
7
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)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,007
Messages
5,526,247
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top