Retrieving first, second and following numerical values of multiple columns

[Nbisgaard]

Hey Excel Forum.

I have a list of items in column A that I have sorted in 3 different groups, as shown in the attached image. After having grouped the items, I want to combine the items in a single column without the empty cells and #N/A cells.

So looking at the picture, I want to create a new column where cell X1 will show 6, X2 will show 7, then 13, 20 etc. all the way to 49. The next number should then be the first number of Group 2, i.e 2, followed by 3 and so forth.

Is there a way to get this done.

All I have so far is

=INDEX(N2:N200;MATCH(1;INDEX(ISNUMBER(N2:N200)+0;0);0))

Which looks through Group 1 and retrieves the first value.

Any ideas how I can progress, and get the remaining values?

Hope it makes a bit of sense after all.

Edit: Added excel and JPEG files.

The "Sorted" values are found in columns N:Q. Column R is where I have tried to make one column with the sorted values.

[Tony Valko]

Many members are unable to see images in the *.png format.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[Nbisgaard]

My apologize, I added a Jpg and a sample excel file for people to check out.

[shukla.ankur281190]

Try
R2=INDEX(N$3:N$56,SMALL(IF(ISNUMBER(N$3:N$56),ROW($N$3:$N$56)-ROW($N$2)),ROW(1:1))) with CTRL+SHIFT+ENTER ( It is an array formula) and drag down.

Please Login or Register  to view this content.

or

R2=INDEX(N$3:N$56,AGGREGATE(15,6,ROW($N$3:$N$56)/(ISNUMBER(N$3:N$56))-2,ROW(1:1))) and drag down.( It's non array formula)

Please Login or Register  to view this content.

[Nbisgaard]

Aha, that was clever. I had given the small function some thought but couldn't see how it could work.

Using said formula I can a column for each of the sorted groups, as shown in the image below, with all the #N/A's filtered out. The final step is then to try and combine the four columns into one. Any ideas how this step could be done?

So the "Final sorting" column will show,
6,7,13 .... , 53, 54, 2,3,9 ... , 51, 47, 54, 1, 4 etc..

[shukla.ankur281190]

Can you tell us that what actual digits will show after final shorting ??

[Nbisgaard]

Yes, ofcourse.
The ultimate goal is to create a column that shows the following:

Please Login or Register  to view this content.

Edit: I see I made a small error in my sorting columns, so item 54 appeared twice..

The pic below shows the corrected, sorted values.

[shukla.ankur281190]

Try
V3=IFERROR(IFERROR(IFERROR(INDEX($R$3:$R$56,ROWS($X$2:X2)),INDEX($S$3:$S$56,ROWS($X$2:X2)-ROWS($R$3:$R$15))),INDEX($T$3:$T$56,ROWS($X$2:X2)-ROWS($R$3:$R$15)-ROWS($S$3:$S$24))),INDEX($U$3:$U$56,ROWS($X$2:X2)-ROWS($R$3:$R$15)-ROWS($S$3:$S$24)-ROWS($T$3:$T$4)))

Please Login or Register  to view this content.

[Nbisgaard]

Thanks a lot, it ended up working quite well, with very little manual work.
Should anyone be interested in the final solution then I have added the excel file.

[shukla.ankur281190]

Glad to help you .... Please mark as it solved and if you liked my solution you can add my reputation by clicking on star * icon.

[syngenta]

@Ankur Shukla,

Little changes post #8 formula

Please Login or Register  to view this content.

[shukla.ankur281190]

Yeah That is nice. I have to need add rep for clean!!!