VSTACK/HSTACK/IF

[gvoisard]

Sheet1:

Sheet: 123:

Basically I need a formula that takes all the lines that have 123 in column "I" on sheet1 to fill on sheet "123" starting in B12. I need columns K:Q to transfer over as well as the Debit/Credit columns. However, instead of the description column from Sheet1, I would like the Condensed Description to transfer to the description column on sheet "123". I do not want the rest of the columns to transfer from Sheet1.



Let me know if you have any questions, I appreciate the help!

 

[Fluff]

Make sure that the headers in sheet 123 exactly match those in sheet1 & then you can use

=FILTER(CHOOSECOLS(Sheet1!F6:Q1000,XMATCH(B10:K10,Sheet1!F5:Q5)),Sheet1!I6:I1000=B2)

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

A similar option but not requiring exactly matching headers would be something like this. You may have to amend/reorder the columns of choice.

=CHOOSECOLS(FILTER(Sheet1!A6:Q8,Sheet1!I6:I8=B2),11,12,13,14,15,16,17,7,8,6)

 

[gvoisard]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

A similar option but not requiring exactly matching headers would be something like this. You may have to amend/reorder the columns of choice.

=CHOOSECOLS(FILTER(Sheet1!A6:Q8,Sheet1!I6:I8=B2),11,12,13,14,15,16,17,7,8,6)

Peter, I could not get that formula to work. Please see the attached spreadsheet and note that I had to add a few columns (although they can be ignored, just wanted to add because it affected the formula) and I also changed the names of the sheets. Feel free to make a copy and reshare.

Thank you.

VSTACK AND HSTACK Q.xlsx

 

[Fluff]

Which of the two "000" columns do you want to return?

 

[gvoisard]

Which of the two "000" columns do you want to return?

Both, but if you have to change one of the names of the columns, then go ahead. Just make sure you change on both sheets.

 

[Fluff]

Ok, how about

VSTACK AND HSTACK Q.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4
5	Journal Source	Journal Name	Invoice Number	Supplier Name	Description	Condensed Description	Debit	Credit	27601	27602	27603	27604	27605	27606	27607	On JV?	OOB	BU	LOCATION	DEPARTMENT	NATURAL ACCOUNT	000	IC BU	001
6	Pay		123	ABC	DEF	XYZ	100									123		1111	222	3333	444444	555	6666	777
7	Pay		567	DEF	GHI	JKL		100								124		5555	666	7777	888888	999	0000	000
8	Pay		456	QWE	RTY	UIO		100								123		9999	888	7777	666666	555	4444	333
9
Checks

VSTACK AND HSTACK Q.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Journal	123
3
4
5
6
7
8
9
10	Line	BU	LOCATION	DEPARTMENT	NATURAL ACCOUNT	000	IC BU	001	DEBIT	CREDIT	DESCRIPTION
11		1-4	5-7	8-11	12-19	20-22	23-26	27-29
12	10	1111	222	3333	444444	555	6666	777	100	0	XYZ
13	20	9999	888	7777	666666	555	4444	333	0	100	UIO
14	30
27601 (With Formula)

Cell Formulas
Range	Formula
B12:K13	B12	=FILTER(CHOOSECOLS(Checks!F6:X1000,XMATCH(B10:J10,TRIM(Checks!F5:X5)),1),Checks!P6:P1000=B2)
Dynamic array formulas.

 

[gvoisard]

I got this to work on the excel spreadsheet that I gave you, however, it does not work on my actual sheet. It is fairly identical except I have values (or #N/A errors in some case) in the blank cells on the checks sheet. Can you think of any reasons for this? I made sure that all the columns are the same and the sheet names are the same as the example.

 

[Fluff]

The formula won't effect what is on the Checks sheet, so not quite sure what you are saying.

 

[gvoisard]

The formula won't effect what is on the Checks sheet, so not quite sure what you are saying.

I figured it out. I had #N/A in column P so I added an IFNA function and it worked. Thanks.