Need an INDEX function to create a horizontal array fron a two dimensional pivot table for merging data into forms

[avoiss]

Hi - i am using a 6k record flat file with submissions records that I have turned into a pivot table for a two dimensional array, but need to get the associated content for each submission using index and match from the submission ID - in some cases there are 1000 associated records per submission ID. I need to add the associated content into columns so I can then extract all the data into merge forms.

This is two days and counting and I am not getting anywhere. At this point I can at least get data, but it is exactly the same across the rows. I have created a small subset of data for testing and here is where I have ended up with the formula.

=INDEX(testtable[RPN],MATCH($F4,testtable[SubmissionID]))

1 2 3
SubmissionID Name RPN
123 SubOne 11111 SubmissionID Name Count of RPN RPN1 RPN2 RPN3 RPN4 RPN5 RPN6 RPN7
123 SubOne 22222 123 SubOne 7 77777 77777 77777 77777 77777 77777 77777
123 SubOne 33333 555 SubTwo 5 133332 133332 133332 133332 133332 133332 133332
123 SubOne 44444 998 SubThree 3 166665 166665 166665 166665 166665 166665 166665
123 SubOne 55555
123 SubOne 66666
123 SubOne 77777
555 SubTwo 88888
555 SubTwo 99999
555 SubTwo 111110
555 SubTwo 122221
555 SubTwo 133332
998 SubThree 144443
998 SubThree 155554
998 SubThree 166665

 

[Jerry Sullivan]

Hi avoiss,

Are the actual RPN values numeric as they are in your example? If so, you could get your desired result with a PivotTable.

 

[avoiss]

Hi - the original table is a 6k flat file for a submission ID for each one-to-many RPN. I created a Pivot table to do that association, but I need to get the RPNs now into one horizontal line with its associated submission file under headings RPN1, RPN2, RPN3, RPN4 .... RPN 350. Can I upload my test file for you to review somewhere?

 

[Jerry Sullivan]

I think I understand the layout of your source data and what you are wanting to do. I asked whether the actual RPN values are numeric as they are in your example because if they are you report horizontally in datafields of a Pivot. If the actual RPNs are a mix of letters and numbers, then a PivotTable isn't feasible.

 

[avoiss]

Sorry - I get it - The RPNs are alphanumeric, quite lengthy, and in some cases contain hyphens.

 

[Jerry Sullivan]

Would a VBA macro solution work for you? This can be done with formulas, but for me it's easier to do with VBA.

 

[avoiss]

Hi - my preference would be a formula so we don't have to switch to XLM - but certainly not required - I will be just so happy to have a solution.....

 

[Jerry Sullivan]

Here's a formula-based solution. Assuming that you have a source data table named "testtable" beginning at Cell A1...

Excel 2013
	A	B	C
1	SubmissionID	Name	RPN
2	123	Sub One	11111
3	123	Sub One	22222
4	123	Sub One	33333
5	123	Sub One	44444
6	123	Sub One	55555
7	123	Sub One	66666
8	123	Sub One	77777
9	555	Sub Two	88888
10	555	Sub Two	99999
11	555	Sub Two	111110
12	555	Sub Two	122221
13	555	Sub Two	133332
14	998	Sub Three	144443
15	998	Sub Three	155554
16	998	Sub Three	166665
Sheet1

Enter these formulas then copy down and across....

Excel 2013
	E	F	G	H	I	J	K	L	M	N	O
1	SubmissionID	Name	Count of RPN	RPN1	RPN2	RPN3	RPN4	RPN5	RPN6	RPN7	RPN8
2	123	Sub One	7	11111	22222	33333	44444	55555	66666	77777
3	555	Sub Two	5	88888	99999	111110	122221	133332
4	998	Sub Three	3	144443	155554	166665
Sheet1

Cell Formulas
Range	Formula
F2	=IF(LEN($E2),VLOOKUP($E2,testtable[[SubmissionID]:[Name]],2,0),"")
G2	=IF(LEN($E2),COUNTIF(testtable[SubmissionID],$E2),"")
E2	{=IFERROR(INDEX(testtable[SubmissionID], SMALL(IF(FREQUENCY(IF(testtable[SubmissionID]<>"", MATCH(testtable[SubmissionID],testtable[SubmissionID],0)), ROW(testtable[SubmissionID])-ROW($A$2)+1), ROW(testtable[SubmissionID])-ROW($A$2)+1),ROWS(E$2:E2))),"")}
H2	{=IF(AND(LEN($E2),COLUMNS($H2:H2)<=$G2),INDEX(testtable[RPN],SMALL(IF($E2=testtable[SubmissionID], ROW(testtable[RPN])-ROW(C$2)+1),COLUMNS($H2:H2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[avoiss]

Hi Jerry - I don't know how to thank you! This is a very elegant solution!!! I cannot thank you enough!!!!

I know you are incredibly busy - so if and when you can spare a moment - can you help me understand the H2 CSE formula you have brilliantly developed above? - I have the C+S+E Mastering Excel Array Formulas book - is there a reference in the book you can point me to and I can review?

 

[Jerry Sullivan]

I don't have the CSE Mastering Excel Array Formulas book. However, based on a workbook of examples from the book I found online, I think the formulas using SMALL() in Chapter 19 are similar. The primary differences are the book's example matches 2 criteria instead of 1, and the unique matches were listed vertically instead of horizontally.

If you have any questions about how the formula I showed in H2 works, I'd be glad to try to explain it.