Excel 6 Columns Comparison
up vote
-3
down vote
favorite
I am trying to compare 6 columns, 3 columns in the One Sheet and 3 columns in theOther Sheet.
Thank you
excel vba excel-vba excel-formula
asked Sep 2 at 20:07
MyExcelJoe
145
|
show 1 more comment
up vote
-3
down vote
favorite
I am trying to compare 6 columns, 3 columns in the One Sheet and 3 columns in theOther Sheet.
Thank you
excel vba excel-vba excel-formula
asked Sep 2 at 20:07
MyExcelJoe
145
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44
|
show 1 more comment
up vote
-3
down vote
favorite
up vote
-3
down vote
favorite
I am trying to compare 6 columns, 3 columns in the One Sheet and 3 columns in theOther Sheet.
Thank you
excel vba excel-vba excel-formula
asked Sep 2 at 20:07
MyExcelJoe
145
I am trying to compare 6 columns, 3 columns in the One Sheet and 3 columns in theOther Sheet.
Thank you
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
asked Sep 2 at 20:07
MyExcelJoe
145
asked Sep 2 at 20:07
MyExcelJoe
145
edited Nov 9 at 4:29
asked Sep 2 at 20:07
MyExcelJoe
145
asked Sep 2 at 20:07
MyExcelJoe
145
asked Sep 2 at 20:07
MyExcelJoe
145
145
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44
|
show 1 more comment
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
This needs some re-factoring. This will repeat results for repeated items in orders.It uses dictionaries to hold the sums by stores using the items as keys.
Option Explicit
Public Sub GetInventoryForListedItems()
Application.ScreenUpdating = False
Dim wb As Workbook, orders As Worksheet, inventory As Worksheet
Set wb = ThisWorkbook
Set orders = wb.Worksheets("Orders")
Set inventory = wb.Worksheets("Inventory")
Dim store1Dict As Object, store2Dict As Object, store3Dict As Object, store4Dict As Object, orderList As Object
Set store1Dict = CreateObject("Scripting.Dictionary")
Set store2Dict = CreateObject("Scripting.Dictionary")
Set store3Dict = CreateObject("Scripting.Dictionary")
Set store4Dict = CreateObject("Scripting.Dictionary")
Set store5Dict = CreateObject("Scripting.Dictionary")
Set store6Dict = CreateObject("Scripting.Dictionary")
Set orderList = CreateObject("Scripting.Dictionary")
Dim ordersArray(), inventoryArray(), lastRowOrders As Long, lastRowInventory As Long, i As Long, ordersData As Range
With orders
lastRowOrders = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ordersData = .Range("A2:A" & lastRowOrders)
Select Case lastRowOrders
Case Is < 2
Exit Sub
Case 2
ReDim ordersArray(1, 1): ordersArray(1, 1) = ordersData.Value
Case Else
ordersArray = ordersData.Value
End Select
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1) 'dictionary of the orders to then search for in inventory
orderList(ordersArray(i, 1)) = vbNullString
Next
End With
With inventory
lastRowInventory = .Cells(.Rows.Count, "A").End(xlUp).Row
Select Case lastRowInventory
Case Is < 2
Exit Sub
Case 2
ReDim inventoryArray(1, 3)
inventoryArray(1, 1) = .Range("A2").Value
inventoryArray(1, 2) = .Range("B2").Value
inventoryArray(1, 3) = .Range("C2").Value
Case Else
inventoryArray = .Range("A2:C" & lastRowInventory).Value
End Select
For i = LBound(inventoryArray, 1) To UBound(inventoryArray, 1) 'check if inventory item in orders dictionary
If orderList.Exists(inventoryArray(i, 1)) And IsNumeric(inventoryArray(i, 2)) Then
Select Case inventoryArray(i, 3) ' add to dictionaries based on store
Case 1
store1Dict(inventoryArray(i, 1)) = store1Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 2
store2Dict(inventoryArray(i, 1)) = store2Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 3
store3Dict(inventoryArray(i, 1)) = store3Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 4
store4Dict(inventoryArray(i, 1)) = store4Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 5
store5Dict(inventoryArray(i, 1)) = store5Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 6
store6Dict(inventoryArray(i, 1)) = store6Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
End Select
End If
Next
End With
With orders
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1)
On Error Resume Next
.Cells(i + 1, 11) = store1Dict(ordersArray(i, 1))
.Cells(i + 1, 12) = store2Dict(ordersArray(i, 1))
.Cells(i + 1, 13) = store3Dict(ordersArray(i, 1))
.Cells(i + 1, 14) = store4Dict(ordersArray(i, 1))
.Cells(i + 1, 15) = store5Dict(ordersArray(i, 1))
.Cells(i + 1, 16) = store6Dict(ordersArray(i, 1))
On Error GoTo 0
Next
.Range("E2:J" & lastRowOrders).Replace What:="", Replacement:="Not found"
End With
Application.ScreenUpdating = True
End Sub
answered Sep 2 at 21:16
QHarr
28k81839
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
This needs some re-factoring. This will repeat results for repeated items in orders.It uses dictionaries to hold the sums by stores using the items as keys.
Option Explicit
Public Sub GetInventoryForListedItems()
Application.ScreenUpdating = False
Dim wb As Workbook, orders As Worksheet, inventory As Worksheet
Set wb = ThisWorkbook
Set orders = wb.Worksheets("Orders")
Set inventory = wb.Worksheets("Inventory")
Dim store1Dict As Object, store2Dict As Object, store3Dict As Object, store4Dict As Object, orderList As Object
Set store1Dict = CreateObject("Scripting.Dictionary")
Set store2Dict = CreateObject("Scripting.Dictionary")
Set store3Dict = CreateObject("Scripting.Dictionary")
Set store4Dict = CreateObject("Scripting.Dictionary")
Set store5Dict = CreateObject("Scripting.Dictionary")
Set store6Dict = CreateObject("Scripting.Dictionary")
Set orderList = CreateObject("Scripting.Dictionary")
Dim ordersArray(), inventoryArray(), lastRowOrders As Long, lastRowInventory As Long, i As Long, ordersData As Range
With orders
lastRowOrders = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ordersData = .Range("A2:A" & lastRowOrders)
Select Case lastRowOrders
Case Is < 2
Exit Sub
Case 2
ReDim ordersArray(1, 1): ordersArray(1, 1) = ordersData.Value
Case Else
ordersArray = ordersData.Value
End Select
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1) 'dictionary of the orders to then search for in inventory
orderList(ordersArray(i, 1)) = vbNullString
Next
End With
With inventory
lastRowInventory = .Cells(.Rows.Count, "A").End(xlUp).Row
Select Case lastRowInventory
Case Is < 2
Exit Sub
Case 2
ReDim inventoryArray(1, 3)
inventoryArray(1, 1) = .Range("A2").Value
inventoryArray(1, 2) = .Range("B2").Value
inventoryArray(1, 3) = .Range("C2").Value
Case Else
inventoryArray = .Range("A2:C" & lastRowInventory).Value
End Select
For i = LBound(inventoryArray, 1) To UBound(inventoryArray, 1) 'check if inventory item in orders dictionary
If orderList.Exists(inventoryArray(i, 1)) And IsNumeric(inventoryArray(i, 2)) Then
Select Case inventoryArray(i, 3) ' add to dictionaries based on store
Case 1
store1Dict(inventoryArray(i, 1)) = store1Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 2
store2Dict(inventoryArray(i, 1)) = store2Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 3
store3Dict(inventoryArray(i, 1)) = store3Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 4
store4Dict(inventoryArray(i, 1)) = store4Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 5
store5Dict(inventoryArray(i, 1)) = store5Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 6
store6Dict(inventoryArray(i, 1)) = store6Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
End Select
End If
Next
End With
With orders
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1)
On Error Resume Next
.Cells(i + 1, 11) = store1Dict(ordersArray(i, 1))
.Cells(i + 1, 12) = store2Dict(ordersArray(i, 1))
.Cells(i + 1, 13) = store3Dict(ordersArray(i, 1))
.Cells(i + 1, 14) = store4Dict(ordersArray(i, 1))
.Cells(i + 1, 15) = store5Dict(ordersArray(i, 1))
.Cells(i + 1, 16) = store6Dict(ordersArray(i, 1))
On Error GoTo 0
Next
.Range("E2:J" & lastRowOrders).Replace What:="", Replacement:="Not found"
End With
Application.ScreenUpdating = True
End Sub
answered Sep 2 at 21:16
QHarr
28k81839
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
add a comment |
up vote
1
down vote
accepted
This needs some re-factoring. This will repeat results for repeated items in orders.It uses dictionaries to hold the sums by stores using the items as keys.
Option Explicit
Public Sub GetInventoryForListedItems()
Application.ScreenUpdating = False
Dim wb As Workbook, orders As Worksheet, inventory As Worksheet
Set wb = ThisWorkbook
Set orders = wb.Worksheets("Orders")
Set inventory = wb.Worksheets("Inventory")
Dim store1Dict As Object, store2Dict As Object, store3Dict As Object, store4Dict As Object, orderList As Object
Set store1Dict = CreateObject("Scripting.Dictionary")
Set store2Dict = CreateObject("Scripting.Dictionary")
Set store3Dict = CreateObject("Scripting.Dictionary")
Set store4Dict = CreateObject("Scripting.Dictionary")
Set store5Dict = CreateObject("Scripting.Dictionary")
Set store6Dict = CreateObject("Scripting.Dictionary")
Set orderList = CreateObject("Scripting.Dictionary")
Dim ordersArray(), inventoryArray(), lastRowOrders As Long, lastRowInventory As Long, i As Long, ordersData As Range
With orders
lastRowOrders = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ordersData = .Range("A2:A" & lastRowOrders)
Select Case lastRowOrders
Case Is < 2
Exit Sub
Case 2
ReDim ordersArray(1, 1): ordersArray(1, 1) = ordersData.Value
Case Else
ordersArray = ordersData.Value
End Select
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1) 'dictionary of the orders to then search for in inventory
orderList(ordersArray(i, 1)) = vbNullString
Next
End With
With inventory
lastRowInventory = .Cells(.Rows.Count, "A").End(xlUp).Row
Select Case lastRowInventory
Case Is < 2
Exit Sub
Case 2
ReDim inventoryArray(1, 3)
inventoryArray(1, 1) = .Range("A2").Value
inventoryArray(1, 2) = .Range("B2").Value
inventoryArray(1, 3) = .Range("C2").Value
Case Else
inventoryArray = .Range("A2:C" & lastRowInventory).Value
End Select
For i = LBound(inventoryArray, 1) To UBound(inventoryArray, 1) 'check if inventory item in orders dictionary
If orderList.Exists(inventoryArray(i, 1)) And IsNumeric(inventoryArray(i, 2)) Then
Select Case inventoryArray(i, 3) ' add to dictionaries based on store
Case 1
store1Dict(inventoryArray(i, 1)) = store1Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 2
store2Dict(inventoryArray(i, 1)) = store2Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 3
store3Dict(inventoryArray(i, 1)) = store3Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 4
store4Dict(inventoryArray(i, 1)) = store4Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 5
store5Dict(inventoryArray(i, 1)) = store5Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 6
store6Dict(inventoryArray(i, 1)) = store6Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
End Select
End If
Next
End With
With orders
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1)
On Error Resume Next
.Cells(i + 1, 11) = store1Dict(ordersArray(i, 1))
.Cells(i + 1, 12) = store2Dict(ordersArray(i, 1))
.Cells(i + 1, 13) = store3Dict(ordersArray(i, 1))
.Cells(i + 1, 14) = store4Dict(ordersArray(i, 1))
.Cells(i + 1, 15) = store5Dict(ordersArray(i, 1))
.Cells(i + 1, 16) = store6Dict(ordersArray(i, 1))
On Error GoTo 0
Next
.Range("E2:J" & lastRowOrders).Replace What:="", Replacement:="Not found"
End With
Application.ScreenUpdating = True
End Sub
answered Sep 2 at 21:16
QHarr
28k81839
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
This needs some re-factoring. This will repeat results for repeated items in orders.It uses dictionaries to hold the sums by stores using the items as keys.
Option Explicit
Public Sub GetInventoryForListedItems()
Application.ScreenUpdating = False
Dim wb As Workbook, orders As Worksheet, inventory As Worksheet
Set wb = ThisWorkbook
Set orders = wb.Worksheets("Orders")
Set inventory = wb.Worksheets("Inventory")
Dim store1Dict As Object, store2Dict As Object, store3Dict As Object, store4Dict As Object, orderList As Object
Set store1Dict = CreateObject("Scripting.Dictionary")
Set store2Dict = CreateObject("Scripting.Dictionary")
Set store3Dict = CreateObject("Scripting.Dictionary")
Set store4Dict = CreateObject("Scripting.Dictionary")
Set store5Dict = CreateObject("Scripting.Dictionary")
Set store6Dict = CreateObject("Scripting.Dictionary")
Set orderList = CreateObject("Scripting.Dictionary")
Dim ordersArray(), inventoryArray(), lastRowOrders As Long, lastRowInventory As Long, i As Long, ordersData As Range
With orders
lastRowOrders = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ordersData = .Range("A2:A" & lastRowOrders)
Select Case lastRowOrders
Case Is < 2
Exit Sub
Case 2
ReDim ordersArray(1, 1): ordersArray(1, 1) = ordersData.Value
Case Else
ordersArray = ordersData.Value
End Select
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1) 'dictionary of the orders to then search for in inventory
orderList(ordersArray(i, 1)) = vbNullString
Next
End With
With inventory
lastRowInventory = .Cells(.Rows.Count, "A").End(xlUp).Row
Select Case lastRowInventory
Case Is < 2
Exit Sub
Case 2
ReDim inventoryArray(1, 3)
inventoryArray(1, 1) = .Range("A2").Value
inventoryArray(1, 2) = .Range("B2").Value
inventoryArray(1, 3) = .Range("C2").Value
Case Else
inventoryArray = .Range("A2:C" & lastRowInventory).Value
End Select
For i = LBound(inventoryArray, 1) To UBound(inventoryArray, 1) 'check if inventory item in orders dictionary
If orderList.Exists(inventoryArray(i, 1)) And IsNumeric(inventoryArray(i, 2)) Then
Select Case inventoryArray(i, 3) ' add to dictionaries based on store
Case 1
store1Dict(inventoryArray(i, 1)) = store1Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 2
store2Dict(inventoryArray(i, 1)) = store2Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 3
store3Dict(inventoryArray(i, 1)) = store3Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 4
store4Dict(inventoryArray(i, 1)) = store4Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 5
store5Dict(inventoryArray(i, 1)) = store5Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 6
store6Dict(inventoryArray(i, 1)) = store6Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
End Select
End If
Next
End With
With orders
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1)
On Error Resume Next
.Cells(i + 1, 11) = store1Dict(ordersArray(i, 1))
.Cells(i + 1, 12) = store2Dict(ordersArray(i, 1))
.Cells(i + 1, 13) = store3Dict(ordersArray(i, 1))
.Cells(i + 1, 14) = store4Dict(ordersArray(i, 1))
.Cells(i + 1, 15) = store5Dict(ordersArray(i, 1))
.Cells(i + 1, 16) = store6Dict(ordersArray(i, 1))
On Error GoTo 0
Next
.Range("E2:J" & lastRowOrders).Replace What:="", Replacement:="Not found"
End With
Application.ScreenUpdating = True
End Sub
answered Sep 2 at 21:16
QHarr
28k81839
This needs some re-factoring. This will repeat results for repeated items in orders.It uses dictionaries to hold the sums by stores using the items as keys.
Option Explicit
Public Sub GetInventoryForListedItems()
Application.ScreenUpdating = False
Dim wb As Workbook, orders As Worksheet, inventory As Worksheet
Set wb = ThisWorkbook
Set orders = wb.Worksheets("Orders")
Set inventory = wb.Worksheets("Inventory")
Dim store1Dict As Object, store2Dict As Object, store3Dict As Object, store4Dict As Object, orderList As Object
Set store1Dict = CreateObject("Scripting.Dictionary")
Set store2Dict = CreateObject("Scripting.Dictionary")
Set store3Dict = CreateObject("Scripting.Dictionary")
Set store4Dict = CreateObject("Scripting.Dictionary")
Set store5Dict = CreateObject("Scripting.Dictionary")
Set store6Dict = CreateObject("Scripting.Dictionary")
Set orderList = CreateObject("Scripting.Dictionary")
Dim ordersArray(), inventoryArray(), lastRowOrders As Long, lastRowInventory As Long, i As Long, ordersData As Range
With orders
lastRowOrders = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ordersData = .Range("A2:A" & lastRowOrders)
Select Case lastRowOrders
Case Is < 2
Exit Sub
Case 2
ReDim ordersArray(1, 1): ordersArray(1, 1) = ordersData.Value
Case Else
ordersArray = ordersData.Value
End Select
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1) 'dictionary of the orders to then search for in inventory
orderList(ordersArray(i, 1)) = vbNullString
Next
End With
With inventory
lastRowInventory = .Cells(.Rows.Count, "A").End(xlUp).Row
Select Case lastRowInventory
Case Is < 2
Exit Sub
Case 2
ReDim inventoryArray(1, 3)
inventoryArray(1, 1) = .Range("A2").Value
inventoryArray(1, 2) = .Range("B2").Value
inventoryArray(1, 3) = .Range("C2").Value
Case Else
inventoryArray = .Range("A2:C" & lastRowInventory).Value
End Select
For i = LBound(inventoryArray, 1) To UBound(inventoryArray, 1) 'check if inventory item in orders dictionary
If orderList.Exists(inventoryArray(i, 1)) And IsNumeric(inventoryArray(i, 2)) Then
Select Case inventoryArray(i, 3) ' add to dictionaries based on store
Case 1
store1Dict(inventoryArray(i, 1)) = store1Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 2
store2Dict(inventoryArray(i, 1)) = store2Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 3
store3Dict(inventoryArray(i, 1)) = store3Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 4
store4Dict(inventoryArray(i, 1)) = store4Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 5
store5Dict(inventoryArray(i, 1)) = store5Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
Case 6
store6Dict(inventoryArray(i, 1)) = store6Dict(inventoryArray(i, 1)) + inventoryArray(i, 2)
End Select
End If
Next
End With
With orders
For i = LBound(ordersArray, 1) To UBound(ordersArray, 1)
On Error Resume Next
.Cells(i + 1, 11) = store1Dict(ordersArray(i, 1))
.Cells(i + 1, 12) = store2Dict(ordersArray(i, 1))
.Cells(i + 1, 13) = store3Dict(ordersArray(i, 1))
.Cells(i + 1, 14) = store4Dict(ordersArray(i, 1))
.Cells(i + 1, 15) = store5Dict(ordersArray(i, 1))
.Cells(i + 1, 16) = store6Dict(ordersArray(i, 1))
On Error GoTo 0
Next
.Range("E2:J" & lastRowOrders).Replace What:="", Replacement:="Not found"
End With
Application.ScreenUpdating = True
End Sub
answered Sep 2 at 21:16
QHarr
28k81839
edited Sep 3 at 15:01
answered Sep 2 at 21:16
QHarr
28k81839
answered Sep 2 at 21:16
QHarr
28k81839
answered Sep 2 at 21:16
QHarr
28k81839
28k81839
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
add a comment |
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Hi QHarr I hope you can take a look at the new question I posted today. Thank you
– MyExcelJoe
Sep 17 at 6:45
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
Looks like you have answers. If not,can you edit your question to make clear what is missing still?
– QHarr
Sep 17 at 7:36
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
QHarr I uploaded 2 pictures to explain my issue, and one in particular explains why the proposed solutions don't work. My original code works fine in pasting the result in the other Sheet, just it does not fill the other rows below when the macro is repeated. I think it's over writing. Thank you
– MyExcelJoe
Sep 18 at 0:09
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
I have uploaded another picture to better explain the issue with the solutions proposed by the others.
– MyExcelJoe
Sep 19 at 3:35
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
Hello QHarr I just posted a new question. I hope you can take a look at it... I posted a picture to explain what I need.
– MyExcelJoe
Nov 11 at 4:33
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52140639%2fexcel-6-columns-comparison%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Maybe you should provide an easier example. Why is store 4 show not found? I see it on both lists. Try to create a smaller example that is easier to follow.
– urdearboy
Sep 2 at 20:21
Thank you Urdearboy for your comment. Unfortunately the situation is even more complicated, I tired to make it easier in my example. Store 4 is showing "Not Found" because it's not listed in the Inventory Sheet for those items. Orange is not listed in the Inventory Sheet, that's why it's showing "Not Found" for all the Stores in the Orders Sheet. Thank you.
– MyExcelJoe
Sep 2 at 20:29
Agh. Sounds like a job for COUNTIFS which would yield 0 instead of “Not Found” for blank items
– urdearboy
Sep 2 at 20:35
Part of the problem is that many times items are not even listed at all in the Inventory Sheet, while other times are listed only for some stores.
– MyExcelJoe
Sep 2 at 20:42
Then the count will be 0 - I don’t see the problem with that unless I’m missing something
– urdearboy
Sep 2 at 20:44