Solved In Excel, how to transfer item according to description

September 10, 2012 at 16:18:22
Specs: Windows 7
In Excel, is it possible to device a formula that would transfer product number according to product name and location. I would like to transfer the product number to either columns, e,f,g, and or h....depending on the product location. For product name, Cargil Salt, I would like product number, 741 to appear in column e, the yellow section, on row 3. product number 789 in f column, the purple section on row 3. Product number 456 in column g, the orange section row 3. Product number 124 in column 6e etc in the green section...
A - B - C


1) Product # , Description , Location
2) 123 , alpha , Green
3) 124 , alpha , Blue
4) 125 , alpha , Red
5) 111 , alpha , Orange
6) 121 , beta , Green
7) 141 , beta , blue


E , F , G , H
Yellow ,Purple ,Orange ,Green
1)
2)
3)
4)
5)
6)
7)


See More: In Excel, how to transfer item according to description

Report •


✔ Best Answer
September 11, 2012 at 18:29:53
This code should produce the results you are looking for, based on the example data provided.

Option Explicit
Sub Match_ID_Color()
Dim lastRw, srcRw, dstRw, col As Integer

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

'Clear color range before each run
   Range("D2:G" & lastRw).ClearContents

'Loop through Source Rows
     For srcRw = 2 To lastRw

'Lock Destination Row for each new group
      dstRw = srcRw

'Find Matching Column For Color, Copy Product #
matchAgain:
          col = WorksheetFunction.Match(Cells(srcRw, 3), Range("$D$1:$G$1"), 0)
            Cells(dstRw, col + 3) = Cells(srcRw, 1)

'If next Product ID is the same, increment Source Row
'and go to match new color
            If Cells(srcRw, 2) = Cells(srcRw + 1, 2) Then
              srcRw = srcRw + 1
               GoTo matchAgain
            End If

 'If next Product ID is the not the same, continue For-Next loop
     Next
End Sub

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



#1
September 10, 2012 at 19:39:11
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

After you have read the instructions, please repost your data so that it is easier for us to read.

Second, I'm confused by your question as it relates to the example data that you posted.

When I look at your example data, I don't see Cargil Salt or any of the numbers in your question, except for 124. Please rephrase your question and try again.

Thanks.

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


Report •

#2
September 11, 2012 at 09:20:58
Thanks for the feedback. I think I may have also confused myself.

What I want to do is organize the file to one line according to the product name in Column B and by Location in Column C and copy the product # to the new color coded columns (columns d - g). Since the product name for lines 2, 3, and 4 is the same I would like the product # to appear on line 2 according to the color coded location. Product # for line A2 copied to column D2. The product # for line A3 copied to E2. The product # from row A4 copied to G2.
Since the next line holds a new product name copy all related product # from line 5 through line 8 to line 5 according to location. The product # from A5 would be copied to E5....



   A              B          C        D          E        F            G   
1)Product #Product Name	Location  Orange	Blue	  Yellow	Green
2) 125	cargil salt	orange				
3) 123	cargil salt	blue 				
4) 124	cargil salt	green				
5) 126	cargil sugar	blue 				
6) 128	cargil sugar	yellow				
7) 17	cargil sugar	green				
8) 213	cargil sugar	orange				
9) 212	auero90	blue 				
10) 211	auero90	yellow				
11) 215	auero90	green				
12) 218	auero50	green				


Report •

#3
September 11, 2012 at 13:37:40
Will there ever be an instance where there will only be a single location for a given Product Name, such as Row 12, where there is only 1 entry for auer050?

I wrote some code that works as long as there are at least 2 locations for each Product Name. I'd need to spend some time making it work for single location rows but I don't want to waste time if that situation will never exist.

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


Report •

Related Solutions

#4
September 11, 2012 at 14:16:55
There will be instances where there is only one. There can be a min of 1 and a max of 4

Report •

#5
September 11, 2012 at 18:29:53
✔ Best Answer
This code should produce the results you are looking for, based on the example data provided.

Option Explicit
Sub Match_ID_Color()
Dim lastRw, srcRw, dstRw, col As Integer

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

'Clear color range before each run
   Range("D2:G" & lastRw).ClearContents

'Loop through Source Rows
     For srcRw = 2 To lastRw

'Lock Destination Row for each new group
      dstRw = srcRw

'Find Matching Column For Color, Copy Product #
matchAgain:
          col = WorksheetFunction.Match(Cells(srcRw, 3), Range("$D$1:$G$1"), 0)
            Cells(dstRw, col + 3) = Cells(srcRw, 1)

'If next Product ID is the same, increment Source Row
'and go to match new color
            If Cells(srcRw, 2) = Cells(srcRw + 1, 2) Then
              srcRw = srcRw + 1
               GoTo matchAgain
            End If

 'If next Product ID is the not the same, continue For-Next loop
     Next
End Sub

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


Report •

#6
September 12, 2012 at 15:47:01
Thank you so much. I appreciate your time and effort.

Report •


Ask Question