get data from multiple cells where shading doesn't exist and make one list from many

Hi experts,

I have a need to get information from multiple lists. These are simply lists of a description and a quantity. They are all on the same tab, and the format is fixed, so it won't change size or content, except for the shading and quantities.

I need to get, automatically, a simplified list of only the descriptions and quantities from the cells that aren't shaded and ignore the ones that are shaded.

To make it more 'fun' the lists are laid out next to each other, beneath and not of equal length, as in the demo file attached.

Hope this explains what is required here.

Thanks.

At first I was going to say that you can't use a formula to detect shading, but when I saw your data I realised that those shaded cells are all empty. You could do it using a few helper columns where these are not used, so put this formula in C3:

=IF(AND(ISNUMBER(B3),A3<>""),MAX(C$2:C2)+1,"-")

and copy this down to C18. It will identify the records which you want to transfer and allocate a unique number to each. The formulae from C3:C18 can be copied into F3 and then I3, but in order to make the numbers run from one column to the next, you need this formula in F2:

=MAX(C:C)

which can be copied into I2.

Then you can use this formula in A22:

=IF(ROWS($1:1)>MAX($I:$I),"",IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$C:$C,0)),IFERROR(INDEX(D:D,MATCH(ROWS($1:1),$F:$F,0)),INDEX(G:G,MATCH(ROWS($1:1),$I:$I,0)))))

Copy it into B22, and then copy both formulae down until you start to get blanks.

Hope this helps.

Pete

Glad to help. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Regarding the rep, don't worry about it. The message means that you can't give consecutive rep to the same person - you need to "spread it around". You must have given rep to me the last time you did so.

Pete