Solved

Need help with an excel file

Posted on 2013-12-18
17
308 Views
Last Modified: 2013-12-19
I have attached an excel file with Sheet1 and Sheet2

I need to keep Sheet1 in tack but only want to keep the WHSE and SKU in Sheet1 that are in Sheet2. Once done I should have the same WHSE and SKU in Sheet1 as in Sheet2 and all other fields remain in tack in Sheet1

Can someone help me with this?
Import.xlsx
0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39728826
to paraphrase your requirements....

1. Delete each row of data in Sheet1 that does NOT have a matching WHSE +  SKU in sheet2 ?

2. for example, the first 8 rows of data would be deleted.  CUPIDS  ALL-1087  is the first matching row....
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39728918
Yes that is correct
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39728954
In a spare column (assume col E) on Sheet2 add this formula, starting at row 2 and copied down:

=A2&B2

This combines the two values.

In a spare column on sheet1 add this formula, starting at row 2 and copy down.

=IFERROR(MATCH(A2&B2,Sheet2!$E:$E,0),"Not matched")

You can then filter on column E and show all those that are Not matched.

If you now select the data as a block and Delete rows, only those visible will be deleted; leaving the hidden ones intact.

Remove filter and remaining lines are visible.

Thanks
Rob H
0
Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39728964
I haven't taken any offence looking at the dataset but some people may have so might have been worth having dummy data (no pun intended).

Regards
Rob
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39728996
You are right! It didn't even cross my mind. Let me try your suggestion
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39729007
Plenty of things crossed my mind when reading down through the list!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39729049
There's nothing wrong with that merch list.  It's very interesting.

@Gerhardpet
Is WHSE 00 in Hillsborough, NC?
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39729052
I have requested to have the file removed. I don't want to make life hard for you :-)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39729069
Just been doing a search on a few of the part numbers and it would appear that they are used by several online suppliers so I don't think it will be an issue.

Should it become an issue, EE Mods will be able to remove the file if you supply a replacement with alternative false data.

I used to work for an Aerospace Company and had an issue with a particular file so uploaded it to EE not realising that it contained proprietary part numbers. The company did regular checks online of use of their part numbers and found the list on EE, uploaded by yours truly. By this time I had left the company but they contacted me and I had to contact EE to get the file replaced.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39729074
No. WHSE is the field name in the database and 00 is the warehouse value

@robhenson
I was wrong in my reply back to you.

I want to delete each row in Sheet1 that DOES have a matching value in Sheet2

Can you help?

Sorry for the confusion.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39729116
Using the method I suggested, the result of the MATCH formula will be a number or "Not matched".

The number is the row number on sheet2.

In the Filter that I described, use the selection dropdown to deselect "Not Matched"; thus showing only those with a number.

Then select all and delete as before, those with Not Matched will be left behind when the Filter is removed.

Thanks
Rob H
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39729118
I think the program in the attached workbook does what you want. Run the procedure 'DeleteUnmatchedRows'.
At the top of the code you will find the following:-
    Private Enum Nws                    ' Worksheet navigation
        NwsFirstDataRow = 2
        NwsWHSE = 1                     ' 1 = column A
        NwsSKU
        NwsMark = 4                     ' Mark item as "Found"
        NwsLookup = 5                   ' This column must be blank
                                        ' in the ImportTab sheet
    End Enum
    
Sub DeleteUnmatchedRows()

    Const DBtab As String = "Sheet1"
    Const ImportTab As String = "Sheet2"

Open in new window

You can change the names of the sheets to match the sheet names in your own workbook.
You may also change all items in the enum Nws.
Note: NwsMark (currently column 4 = column D) is the column where the program will insert the word "Found" if a match was found in the database. After running the program you may like to look for rows which don't have that remark. Make sure that you specify a column that is blank in your source sheet.
NwsLookup (currently column 5 = column E) specifies another column which must be blank. The program uses this column to temporarily store a look-up value. At the end of the program deletes this column.
EXX-131220-Remove-Unmatched-Rows.xlsm
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39729123
Alternatively, if you show only "Not matched", selecting the visible area and copy/pasting to another sheet will only copy/paste the visible rows.

Thanks
Rob H
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39729134
My program removes every line in the database that doesn't have a match in Sheet2. It now seems that you want the opposite. That would be a very small change. Please look for this code:-
        If Rs Then
            WsS.Cells(Rs, NwsMark).Value = "Found"
        Else
            WsT.Rows(Rt).Delete
        End If

Open in new window

Replace it with this code (effectively only removing the line with Else in it):-
        If Rs Then
            WsS.Cells(Rs, NwsMark).Value = "Found"
            WsT.Rows(Rt).Delete
        End If

Open in new window

This code will mark the item as "Found" and delete the row in the database.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39729177
The following code will need to be placed in either an .xlsm or .xls workbook.
Option Explicit

Public Sub Q_28321632()
    Dim dicWS As Object
    Dim vWS() As Variant
    Dim lngLoop As Long
    Dim wksList As Worksheet, rngList As Range
    Dim wksData As Worksheet, rngData As Range
    Dim rng As Range
    
    Set dicWS = CreateObject("scripting.dictionary")
    Set wksList = Worksheets("Sheet2")
    Set wksData = Worksheets("Sheet1")
    vWS = wksList.Range("A1").CurrentRegion.Value
    For lngLoop = 1 To UBound(vWS, 1)
        dicWS.Add vWS(lngLoop, 1) & vWS(lngLoop, 2), 1
    Next
    Set rngData = wksData.Range(wksData.Range("A1"), wksData.Range("A1").End(xlDown))
    Application.ScreenUpdating = False
    For lngLoop = rngData.Rows.Count To 2 Step -1
        If dicWS.exists(rngData.Cells(lngLoop, 1) & rngData.Cells(lngLoop, 2)) Then
            wksData.Rows(lngLoop).Delete
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39729201
Just noticed there may be slight confusion in my instructions above.

I have suggested the WHSE & SKU combination formula in column E on sheet2 and referred to column E in the formula on sheet1.

I have also then referred to column E for applying the Filter. This should have been whichever column you used for the match formula on sheet1.

Apologies.
Rob H
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 39730845
Thanks Rob! This worked just like I needed it to
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question