Solved

Need help with an excel file

Posted on 2013-12-18
17
276 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
  • 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 31

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
 
LVL 31

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 31

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

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 31

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 31

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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now