Solved Aligning Cells with the Same Value in an Adjacent Column

September 25, 2012 at 08:21:38
Specs: Macintosh
I have some sales pricing data for products in our company in one spreadsheet. I have the cost prices for the same products in another spreadsheet.

They are completely unrelated in how they are ordered. The sales pricing spreadsheet has been ordered in a way over the years that most efficiently helps our sales team sell the products and corresponding accessories etc. Our cost prices are ordered by supplier.

I am looking to maintain the order (or sort) of the products in the "sales spreadsheet" but want to take the cost prices and place them in an adjacent column within the sales spreadsheet.

So I have copied the "product" column and "cost price" column from the supplier spreadsheet and pasted them into the sales spreadsheet. I want to now sort these columns so that the "product" column from the supplier spreadsheet aligns with the "product" column from the sales spreadsheet so that cost price of each product is there but the order of the sales spreadsheet has been maintained. There are multiple heading lines in the sales spreadsheet within the "product"column

Here is an example (with altered product names and prices) of what I am looking at and what I would like to achieve -- The 2nd and 3rd columns have been added into the sales spreadsheet. (Please excuse the dots, I dont know how to get the spacing right in the thread. But they are essentially 3 columns)

Product............Product.............Cost Price
.......................DFG-150.............$59
Kettles.............QPR-600.............$30
ABC-100..........RLL-450..............$60
.......................KLL-100.............$15
Stoves............DSD-780.............$10
DFG-150..........HKJ-100.............$25
KLL-100...........ABC-100............$99


After alignment:

Product.............Product.............Cost Price

Kettles
ABC-100............ABC-100.............$99

Stoves
DGF-150............DFG-150.............$59
KLL-100.............KLL-100..............$15

......................QPR-600.............$30
......................RLL-450..............$60
......................DSD-780.............$10
......................HKJ-100..............$25

Any products that don't have a corresponding partner in the adjacent cell must be left blank and pushed down to the bottom of the 2nd column. The order in the first column needs to remain the same. If a product in the first column doesn't have a corresponding product in the 2nd column, the cell must be left blank in the 2nd column

I truly hope this is understandable as I tried to be as thorough as possible. Although I think the concept isn't that complicated I have read several other similar posts in which I realised the very "specific" nature of VBA. So if anyone is kind enough to help me, it would be appreciated BEYOND MEASURE. It will save me days of donkey work physically typing them in as the real list is over 2000 products long.


See More: Aligning Cells with the Same Value in an Adjacent Column

Report •


#1
September 25, 2012 at 09:34:43
re: "(Please excuse the dots, I dont know how to get the spacing right in the thread. But they are essentially 3 columns)

Click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 25, 2012 at 09:51:40
I have read it but the table comes out all misaligned and wonky and unreadable

I have tried numerous times but I just cant get it right :(

It is legible enough though, or does it need to be in that format for someone to extract the sample into excel?


Report •

#3
September 25, 2012 at 10:26:48
✔ Best Answer
First, I'll assume that the DGF-150 in your output should be DFG-150 to match the input.

Second, this code assumes that your data starts in A1.

If both of those are true, this code will produce the aligned list in Columns D:F

Once the list is created, you can delete Columns A:C, or copy the output someplace else, etc. The code will put the text strings in Columns D:F so they are not dependent on the original data once the macro is done.

I suggest that you run this code in a backup copy of your workbook in case something goes horribly wrong. Macros cannot be undone.

<PRE>Sub AlignProducts()
'Determine last row with data
   lastRw = Range("B" & Rows.Count).End(xlUp).Row
'Set first row for orphans
   addRw = lastRw
'Copy Column A to D
   Range("A1:A" & lastRw).Copy _
    Destination:=Range("D1")
'Copy Header row
   Range("B1:C1").Copy _
    Destination:=Range("E1")
'Loop through data in Column B
    For nxtRw = 2 To lastRw
'Search Column A for product from Column B
      With Range("A1:A" & lastRw)
        Set p = .Find(Range("B" & nxtRw))
'If product is found, align it with product in Column D
          If Not p Is Nothing Then
             Range("B" & nxtRw & ":C" & nxtRw).Copy _
               Destination:=Range("E" & p.Row)
'If product is not found, place it at the bottom of Column E
          Else
            addRw = addRw + 1
             Range("B" & nxtRw & ":C" & nxtRw).Copy _
               Destination:=Range("E" & addRw)
          End If
      End With
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 25, 2012 at 10:30:32
     A              B             C
1  Product       Product      Cost Price
2                DFG-150        $59
3  Kettles       QPR-600        $30
4  ABC-100       RLL-450        $60
5                KLL-100        $15
6  Stoves        DSD-780        $10
7  DFG-150       HKJ-100        $25
8  KLL-100       ABC-100        $99

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
September 25, 2012 at 10:47:00
@DerbyDad03

Thank you SO much for this -- It seems to work relatively well so far, but I am not sure 100% so I am gna troll through the data and check and come back to confirm.

Again, THANK YOU


Report •

#6
September 25, 2012 at 13:30:55
Okay so I've run through it and it seems as if it partially works.

I cant seem to put my finger on why one product aligns and another doesn't.
There are some products that are listed at the bottom of the column implying that they couldn't find a corresponding match in the original column. But when executing "find", typing in the product code finds it in the first column, then 2nd "pasted in" column and the new generated column with the alignments (at the bottom without being aligned).

I made sure all the cells were formatted the same (as text) and even their alignment within the cells are the same. Thank you for what you have done so far, is there any other information I can give to maybe solve the problem?


Report •

#7
September 25, 2012 at 13:50:36
I suspect that it may be because the data in column A is longer(more spread out and more data) than the data in column B.

My apologies for not mentioning this before :(


Report •

#8
September 25, 2012 at 14:19:41
Figured it out.

By putting in a cell with just some random text in it in the last line of data in line with column A it runs the macro for the whole sheet...

So your macro works perfectly first time.

THANK YOU!


Report •

#9
September 25, 2012 at 16:41:44
Based on your original example data, I assumed that all columns had the same amount of data. I needed to choose a Column to determine how long the data set was, so I chose Column B since it appeared to be filled with data. It was kind of a random choice.

Since Column B was "shorter" than Column A, the row number used as the "lastRw" was the last row in Column B, therefore the macro was not searching all of the data in Column A.

Instead of adding data to Column A, you can change this line:

'Determine last row with data
   lastRw = Range("B" & Rows.Count).End(xlUp).Row

to be:

'Determine last row with data
   lastRw = Range("A" & Rows.Count).End(xlUp).Row

that way the code will use Column A to set the "last row" variable.

You can also change this line:

'Set first row for orphans
   addRw = lastRw

to be:

'Set first row for orphans
   addRw = lastRw + 1

To add the blank row between the end of your known product numbers and your orphans. I missed that in the first version.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question