Help Please - Vlookup problem

[baz]

I am using a vlookup to grab some data from a master list. My vlookup
works fine but here is the dilemma:

I am using a sheet that has retail items down the rows and divisions
where certain items are authorized. Each item has various information
including UPC numbers, which I am using for my vlookup. The
dilemma/problem/@#!$%%$#@ is that in several instances there may be
two or three lines of the item listed. What I get is say three rows
with the same UPC but different points of distribution. Obviously the
vlookup will only find the first instance of the UPC and give me back
the values in that row.

I am wondering if there is a way to (easily/automated) combine all
rows of those instances resulting in one row with all of the
distribution points in that row so I could get a correct readout.

EG: The distribution points are A, B, C, D, E, F, G, H, I, J and
across the top in individual columns. All rows have the same UPC
(biggest unique identifier)
Row A3 shows an "x" in A, and F
Row A4 shows an "x" in B, C, D, G, H, and I
Row a5 shows an "x" in J


I want one row amalgamating all of the rows into one row so my vlookup
will pull data from that and be credible.

Please post or respond to bzemcik<no Spam>at<no Spam>yahoo dot (k)om
The email for response from this posting is bogus to prevent Spam.

Thanks,

baz

 

[Alan Beban]

You should post the output you expect to get, particularly if two rows
have x's in the same column.

Alan Beban

 

[baz]

Sorry Alan.

The expected output would be:
Row A3 shows an "x" in A,B,C,D,F,G,H,I,J (there never was an "x" in E.

Thanks,

baz

 

[Alan Beban]

And what is Row A4 going to show after the change? Row 5? And what if
there were an x in Columns C and J of Row A5, instead of just in Column
J? I.e., two rows have x's in the same column, as I mentioned in my
last post.

Alan Beban

 

[baz]

Alan:

I just want a compilation of "x's" in the correct columns to be at the
top so it will be read by my vlookup. As to the subsequent rows with
the same UPC, I don't care since they won't be read anyway.

As to the duplications of x's in multiple rows As long as there isan
"x" in the compilation row, that is my only concern. This isa good
question but I'm not sure it exist s in this particular report.

Thanks for the time you are taking on this. I do appreciate it.

baz

 

[Dave Peterson]

It sounds like you want an X in any column that has at least one X in that
"group" of upc's.

Copy that worksheet that contains the lookup table to a new worksheet. (This
macro will kill the original data--so do it against a copy.

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long

Dim myRng As Range
Dim curWks As Worksheet

Set curWks = Worksheets("sheet1")

With curWks
FirstRow = 1 'last header row

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, LastCol))
With myRng
.Value = .Value
On Error Resume Next
.Offset(1, 1).Resize(.Rows.Count - 1, _
.Columns.Count - 1).Cells _
.SpecialCells(xlCellTypeConstants).Value = "X"
On Error GoTo 0
.Sort key1:=myRng(1), order1:=xlAscending, _
header:=xlYes
End With
For iRow = LastRow To FirstRow + 2 Step -1 'avoid the header
If .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value Then
For iCol = 2 To LastCol
If IsEmpty(.Cells(iRow, iCol)) Then
'do nothing
Else
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
.Rows(iRow).Delete
Else
'do nothing special
End If
Next iRow
End With

End Sub

I assumed that column A contained the UPC. That Row 1 contained the headers.
Row 2 started the data.

If I'm wrong, fix the FirstRow = 1 line. (If it doesn't start in column A, make
it start in column A. Heck make it start in Row 1, too!)

It sorts the lookup table by the first column. It then converts any formulas to
values and all those values to X's. then it starts at the bottom looking to see
if the bottom row should be "merged" into the previous row. If it was the same
UPC, then it kills that bottom row (after the merge).

Then off to the next most bottom row.

=======
Awhile ago, there was a discussion that was kind of similar to this. But the
poster actually wanted the values of each match to be returned.

There was a formula and a userdefined function posted. (I like the UDF <bg>.)

You can find it here:
http://groups.google.com/groups?&[email protected]

And if you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Alan Beban]

I have code to revise one UPC, but to do it for all of them I need more
info about the structure of the data. E.g., what does the UPC column
look like? The code I have in mind will revise the rows for one UPC,
then move on to the next, then the next, etc., and I don't know how to
move on.

Alan Beban