Simple spreadsheet help. I have two excel files, need help filtering them together for certain results.?

Question

Okay, let’s see if I can explain this clearly enough. I have 2 excel files, one is just a master listing of some product codes (master listing). The other file is a listing of some of the product codes, along with quantities next to them (inventory listing). Now, what I’m trying to accomplish is to filter these two files together and still have my master listing, but with the product codes next to them. Something like this:

Master Listing file:
500251
500313
500452
500556
500617
500721

Inventory Listing file (42,214, and 7 are quantities):
500313 – 42
500556 – 214
500617 – 7

What I’m trying to accomplish is a spreadsheet that filters the data into the master listing, something like this:
500251
500313 – 42
500452
500556 – 214
500617 – 7
500721

BTW, The quantities are in a different cell than the product codes. I use excel a lot, but this is a little over my head. I’ve also attempted at MS Access, but I’m a serious noob for that program. Any advice? Thanks!

Answer 1

No one will want to help you, 'cause you asked the question three times. Next time just ask once.

Answer 2

Here is a slightly tested you can use. Place it in a blank module and call it from the macros menu.



To do this, go to Tools, Macros, Visual Basic Editor. Click "Insert" >> "Module".



This will run through all lines and check for matches, and copy and paste appropriately. It may or may not work, but it works with the data above and is scalable to work with as many rows you have.



If you have header rows, just change the "1's" to 2's. This assumes all data is in the first two columns.



Copy it exactly:



Sub transport()

'You will need to set this up so both sheets are in the

'same book

'You can name the sheets how you want. The master sheet is

'the one WITHOUT the inventory.

'Send me a message if you need help.

'This script is without warranty or support.



Dim I As Integer

Dim J As Integer

Dim master As String

Dim subsheet As String

master = "master" 'Change this to the master worksheet.

subsheet = "subsheet" 'This is the one with both numbers

ActiveSheet.Range("A1").Select 'This selects the first cell

'in the Master column, assuming there is a header row

ActiveSheet.Range(ActiveCell, (Cells(65536, ActiveCell.Row).End(xlUp))).Select

'This finds the first blank cell in Master column, and selects

'everything before it

For J = 1 To Selection.Rows.Count

For I = 1 To Selection.Rows.Count

If Worksheets(master).Range("A" & J).Value = Worksheets(subsheet).Range("A" & I).Value Then

Worksheets(subsheet).Range("B" & I).Copy _

Destination:=Worksheets(master).Range("B" & J)

End If

Next

Next

End Sub

Answer 3

spotlight the cells you may desire to merge precise click format cells under alignment place a tick mark on merge cells or in case you have a small letter "a" with arrows left and precise on your toolbars, only spotlight the cells and click it. oops... warning! in case you merge them. the suggestions on the different cellular would be deleted.