Hi there
Simple task which has been bugging me for days. Keep coming back to it with a possible solution but it fails.
Two sheets of data
Sheet 1 has inventory
Sheet 2 has product
I need to update the sheet 2 product with the sheet 1 inventory as per example below
Have tried xlookup, sumif, index, match but cant find the solution. next step is to spend a day writing it down on paper and doing it manual then input results into a sheet.
Many thanks in advance.
Simple task which has been bugging me for days. Keep coming back to it with a possible solution but it fails.
Two sheets of data
Sheet 1 has inventory
Sheet 2 has product
I need to update the sheet 2 product with the sheet 1 inventory as per example below
Have tried xlookup, sumif, index, match but cant find the solution. next step is to spend a day writing it down on paper and doing it manual then input results into a sheet.
Many thanks in advance.
Sheet 1 | Sheet 2 | ||||||||
ID | Product | Qty | ID | Apples | Banana | Oranges | Pears | Total | |
100 | Apples | 6 | 100 | ||||||
101 | Oranges | 4 | 101 | ||||||
101 | Pears | 5 | 102 | ||||||
103 | Apples | 1 | 103 | ||||||
103 | Pears | 5 | 104 | ||||||
103 | Oranges | 2 | 105 | ||||||
105 | Oranges | 4 | 106 | ||||||
106 | Apples | 8 | 107 | ||||||
107 | Oranges | 2 | 108 | ||||||
107 | Oranges | 6 | 109 | ||||||
107 | Apples | 4 | 110 | ||||||
109 | Pears | 4 | 111 | ||||||
109 | Oranges | 6 | 112 | ||||||
110 | Pears | 7 | 113 | ||||||
111 | Pears | 4 | 114 | ||||||
112 | Apples | 2 | 115 | ||||||
113 | Apples | 8 | 116 | ||||||
113 | Pears | 1 | 117 | ||||||
113 | Oranges | 9 | 118 | ||||||
115 | Banana | 5 | 119 | ||||||
115 | Oranges | 2 | 120 | ||||||
116 | Apples | 7 | |||||||
116 | Banana | 2 | |||||||
117 | Apples | 5 | |||||||
119 | Apples | 2 |