Combining data from two tables

[ginotx]

I am trying to combine information from Sales History and from Current Availability in order to have printouts available for monthly purchase or production scheduling.

I combined the two tables in colums "A" to "I" --(Code to Sales #2022)
800 plus codes with Availability info and 400 plus codes with sales info.
Many codes are not duplicated because the plants are used in production to grow to the larger sizes but not sold resuling in about 400 code duplicates.
I need to combine the tables so that I have Sales history along with availabiliity for each unique plant code.
I used the unique command in order to have only one code in Colums "K" to "S". The problem is combining the "Availability" and "Sales" numbers.

My problem is that I am unable to combine the numbers in colums "N" to "S" (Ready for Sale to Sales $2020)
I have tried XLookup, and Indexmatch, I am not having any luck getting a correct formula.
I did not try sumif becasue there was no calculations needed, just looking up and transferring the info.
XLookup seems to have issues with the duplicate codes so I tried IndexMatch. I must be overthinking something and making this more difficult than it is.

On the spreadsheet example:

Code 52244067 Picea Abies... is one of the duplicate codes with some data on each listing. There is no math calculations needed, just copying the data from two different lines to a unique code to the right.

Thank you

Sales Analysis Trees.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	CODE	PLANT	SIZE	READY FOR SALE	NOT READY FOR SALE	Sales # 2023	Sales $ 2023	Sales # 2022	Sales $ 2022		CODE	PLANT	SIZE	READY FOR SALE	NOT READY	SALES #2023	SALES $2023	SALES #2022	SALES $2022
2	52046716	Picea Pungens Glauca Fastigiata	8/10' B&B	0	3						52046716	Picea Pungens Glauca Fastigiata	8/10' B&B
3	52210127	Pinus mugo var. pumilio	# 3	3152	0						52210127	Pinus mugo var. pumilio	# 3
4	52210128	Pinus mugo var. pumilio	# 1	800	23840						52210128	Pinus mugo var. pumilio	# 1
5	52210129	Pinus mugo var. pumilio	# 5	0	600						52210129	Pinus mugo var. pumilio	# 5
6	52241484	Pinus mugo var. pumilio	Seedling	0	1600						52241484	Pinus mugo var. pumilio	Seedling
7	52243391	Pinus mugo var. pumilio	15/18"	0	794						52243391	Pinus mugo var. pumilio	15/18"
8	52243631	Pinus mugo var. pumilio	12/15"	0	1800						52243631	Pinus mugo var. pumilio	12/15"
9	52244067	Picea abies 'Nidiformis'	# 3	84	0						52244067	Picea abies 'Nidiformis'	# 3
10	52244067	Picea abies 'Nidiformis'	# 3			92	$ 1,632	10	$ 176		52244081	Pinus mugo var. pumilio	#10
COMBINED DATA SORT

Cell Formulas
Range	Formula
K2:M1012	K2	=UNIQUE(A2:C2000)
Dynamic array formulas.

 

[awoohaw]

Well, it looks like A9 is numeric and A10 is text.
But still you won't get your data from the A10 with a lookup as it will stop at the first record.
One way to approach it is to do what you said you didn't want, here it is summing array formulas. It can probably be done with a filter or sumifs.

Note, I had to make the headers match. They didn't match with I copied your data, I don't know if they already match in your workbook:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	CODE	PLANT	SIZE	READY FOR SALE	NOT READY FOR SALE	Sales # 2023	Sales $ 2023	Sales # 2022	Sales $ 2022		CODE	PLANT	SIZE	READY FOR SALE	NOT READY FOR SALE	Sales # 2023	Sales $ 2023	Sales # 2022	Sales $ 2022
2	52046716	Picea Pungens Glauca Fastigiata	8/10' B&B	0	3						52046716	Picea Pungens Glauca Fastigiata	8/10' B&B	0	3	0	0	0	0
3	52210127	Pinus mugo var. pumilio	# 3	3152	0						52210127	Pinus mugo var. pumilio	# 3	3152	0	0	0	0	0
4	52210128	Pinus mugo var. pumilio	# 1	800	23840						52210128	Pinus mugo var. pumilio	# 1	800	23840	0	0	0	0
5	52210129	Pinus mugo var. pumilio	# 5	0	600						52210129	Pinus mugo var. pumilio	# 5	0	600	0	0	0	0
6	52241484	Pinus mugo var. pumilio	Seedling	0	1600						52241484	Pinus mugo var. pumilio	Seedling	0	1600	0	0	0	0
7	52243391	Pinus mugo var. pumilio	15/18"	0	794						52243391	Pinus mugo var. pumilio	15/18"	0	794	0	0	0	0
8	52243631	Pinus mugo var. pumilio	12/15"	0	1800						52243631	Pinus mugo var. pumilio	12/15"	0	1800	0	0	0	0
9	52244067	Picea abies 'Nidiformis'	# 3	84	0						52244067	Picea abies 'Nidiformis'	# 3	84	0	92	1632.08	10	176.23
10	52244067	Picea abies 'Nidiformis'	# 3			92	1632.08	10	176.23		0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
K2:M10	K2	=UNIQUE(A2:C2000)
N2:S10	N2	=SUM(($D$2:$I$10)*($A$2:$A$10=$K2)*($D$1:$I$1=N$1))
Dynamic array formulas.

 

[ginotx]

I am trying to combine information from Sales History and from Current Availability in order to have printouts available for monthly purchase or production scheduling.

I combined the two tables in colums "A" to "I" --(Code to Sales #2022)
800 plus codes with Availability info and 400 plus codes with sales info.
Many codes are not duplicated because the plants are used in production to grow to the larger sizes but not sold resuling in about 400 code duplicates.
I need to combine the tables so that I have Sales history along with availabiliity for each unique plant code.
I used the unique command in order to have only one code in Colums "K" to "S". The problem is combining the "Availability" and "Sales" numbers.

My problem is that I am unable to combine the numbers in colums "N" to "S" (Ready for Sale to Sales $2020)
I have tried XLookup, and Indexmatch, I am not having any luck getting a correct formula.
I did not try sumif becasue there was no calculations needed, just looking up and transferring the info.
XLookup seems to have issues with the duplicate codes so I tried IndexMatch. I must be overthinking something and making this more difficult than it is.

On the spreadsheet example:

Code 52244067 Picea Abies... is one of the duplicate codes with some data on each listing. There is no math calculations needed, just copying the data from two different lines to a unique code to the right.

Thank you

Sales Analysis Trees.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	CODE	PLANT	SIZE	READY FOR SALE	NOT READY FOR SALE	Sales # 2023	Sales $ 2023	Sales # 2022	Sales $ 2022		CODE	PLANT	SIZE	READY FOR SALE	NOT READY	SALES #2023	SALES $2023	SALES #2022	SALES $2022
2	52046716	Picea Pungens Glauca Fastigiata	8/10' B&B	0	3						52046716	Picea Pungens Glauca Fastigiata	8/10' B&B
3	52210127	Pinus mugo var. pumilio	# 3	3152	0						52210127	Pinus mugo var. pumilio	# 3
4	52210128	Pinus mugo var. pumilio	# 1	800	23840						52210128	Pinus mugo var. pumilio	# 1
5	52210129	Pinus mugo var. pumilio	# 5	0	600						52210129	Pinus mugo var. pumilio	# 5
6	52241484	Pinus mugo var. pumilio	Seedling	0	1600						52241484	Pinus mugo var. pumilio	Seedling
7	52243391	Pinus mugo var. pumilio	15/18"	0	794						52243391	Pinus mugo var. pumilio	15/18"
8	52243631	Pinus mugo var. pumilio	12/15"	0	1800						52243631	Pinus mugo var. pumilio	12/15"
9	52244067	Picea abies 'Nidiformis'	# 3	84	0						52244067	Picea abies 'Nidiformis'	# 3
10	52244067	Picea abies 'Nidiformis'	# 3			92	$ 1,632	10	$ 176		52244081	Pinus mugo var. pumilio	#10
COMBINED DATA SORT

Cell Formulas
Range	Formula
K2:M1012	K2	=UNIQUE(A2:C2000)
Dynamic array formulas.

I appreciate your help.

I believe I understand your solution, but I have had a problem applying your solution on my spreadsheet.

When I try to expand the coverage out to 3000 rows in the event I want to include all inventory I recieve "#VALUE!" as a result. Actually, I am unable to get any data past row 9 where the first row of data posts.

Thank you

 

[awoohaw]

1. For your first question, what do you mean by "inventory", do you mean the entire range.
2. For your second question, you need to adjust your formula to include the new rows. Your example had ten rows, I created a formula for nine rows (excluding the header).

If the answer to 1 is yes, then without knowing what the other data looks like debugging is difficult. Please post an xl2bb of the rows with the #VALUE error.
For 2, change the $10 in all the references to row # below your last record, like $3000 or something.

Are the datatypes in the comparing columns all the same?
Have you confirmed the VALUES column K are exact matches of a VALUE in column A?
Are columns D-I and N-S all numeric?

Please post the errors, or give the formula in the cell that has the error, and the values on the row. But, would be best to post and xl2bb of that area of the worksheet.

 

[ginotx]

I will have to go back and rework the spreadsheet. The weekend update changed the basic report and I have to pull the data out from a different report. Thank you for your help so far, after I recreate the reports to pull from master data, I will post new copies of the fields I am having issues with.

Thank you for your assistance so far.