Moving data around based on Index formula results

[jyadayada]

I have a range in cols A and B, A being cust id and B being order id. there are a varing number or order ids against the same customer id throughout the range

In cols D through H i have data i need to bring these order ids next to, which I'm bringing into column I with the following formula:

=INDEX($B$2:$B$8556, SMALL(IF($H$3241=$A$2:$A$8556, ROW($A$2:$A$8556)-MIN(ROW($A$2:$A$8556))+1, ""), ROW(A1)))

when copied down it brings a range of order ids down through column I until it errors with a #NUM! result, i then manually move the dataset in columns D through H down to the row wher the first #NUM! error is within colimn I. edit the formula to represent the new reference in H and reset the counter to A1 and repeat.

I'm trying to find a way to automate this to save me inserting cells and editing the formula aprox 7000 more times, any suggestions?

[JohnTopley]

Try

=IFERROR(INDEX($B$2:$B$8556, SMALL(IF($H$2=$A$2:$A$8556, ROW($A$2:$A$8556)-ROW($A$2)+1, ""), ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter

[jyadayada]

Hi John

That gives me returns where there are any to return and then blank cells instead of the #NUM! error.

What I'm doing here is building a dataset based on the order ids returned. The data I intend to keep is in columns D to H, with new data (order ids) being brought into column I. The rows in columns D-H represent a customer line with the customer id in it, i want to bring, against each customer as many rows of order ids there are, into column I and then move the rows in columns D-H down to the first blank or #NUM! row and then repeat.

it's the moving of the data in columns d-h to the first #NUM! row and rerunning the edited formula i'm trying to automate.

[JohnTopley]

Post a file to illustrate the problem.

[jyadayada]

try this,...

[JohnTopley]

Sorry but the file doesn't explain to me what you require:

What is starting position - before anything is place in I?

Why do you want to repeat data in I?

Why prefix order number with the text "Order no"?

If you want the Order No in I to match those in A

in I2

=IF($H2<>$A1,B2,B1)

copy down

[jyadayada]

the starting position is just columns d-h without blank rows, there are sometimes blank inn D and E or F,G and H but not both at the same time. This is essentially a customer list.

The data I'm bringing into I, based on the range in A and B is the order numbers each of these customer rows (D-H) has, there are sometimes none but more often anywhere from one order number to over 100.

So the manual version is to run the index formula from above in I, modifying the H column cell ref to the curent row, shift control and enter it and then copy it down, in say the instance of multiple returns in I i then highlight the cells (D-H) against the second result down to the last and insert cells to move the customer data down to the first availiable row (#NUL! in column I), therefore moving the customer data down so that the returned order numbers in column I are just against that cusomer row that returned them

so the starting point is columns D-H with the order numbers stored in the aray A-B, the target being columns D-H, with I populated with corresponding order numbers and D-H being expanded so the rows are alligned forming the basis of a new dataset based on what is currently 2 unaligned sets