extract "unique" records depending one column

[jimapos]

Hello
I need your help guys
I am not able to attach the file

I have this (quantity is the key..)

what I have what I want
quantity Discription price total quantity Discription price total
George 1 apples 50 50 George 1 apples 50 50
anna2 2 bananas 10 20 anna2 1 bananas 10 20
Michael 1 orange 8 8 anna2 1 bananas 10 20
George 1 bananas 10 10 Michael 1 orange 8 8
Steve 3 lemon 7 21 George 1 bananas 10 10
Stella 4 cheese 12 48 Steve 1 lemon 7 21
Steve 1 lemon 7 21
Steve 1 lemon 7 21
Stella 1 cheese 12 48
Stella 1 cheese 12 48
Stella 1 cheese 12 48
Stella 1 cheese 12 48
If anyone can help will ne great..

[Pete_UK]

You should be able to attach a file (and it would be better to do so, as your table is not very clear).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon (attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

[canapone]

Γεια σας!


Please refer to the attachment

In I4

=if(row(A1)<=sum(D$4:D$9),Index($C$4:$C$9,match(Row(A1)-1,index(Subtotal(9,offset(D$3,(Row($4:$9)-4)/(Row($4:$9)-3),,Row($4:$9)-3,1)),))),"")

In K4 to be copied to the right

=vlookup($I4,$C$4:$G$9,column(C$1),0)


Regards

[jimapos]

Hello Canapone

thanks for your answer (wow about "Γεια σας")
The formulas are correct
but i need to copy somehow the entire row c5:g5 to i5:m5 two times if the d5 =2 (true)
copy the entire row c9:g9 to i9:m9 four times if the d9 =4 (true)

[canapone]

Ciao,

I've attached an example.

Try to change number in column D

I've added some Iferror to formulas

Regards

(Ελπίζω ότι καταλαβαίνω!)

Google translator...

[jimapos]

Hello
Thank you very match my friend works like a charm
CIAO..

[samba_ravi]

I4=IF(ROWS($I$4:$I4)>SUM($D$4:$D$9),"",LOOKUP(ROWS($I$4:$I4),SUBTOTAL(9,OFFSET($D$4,,,ROW($D$4:$D$9)-ROW($D$4)+1,))-$D$4:$D$9+1,C$4:C$9))

Please Login or Register  to view this content.

Try this copy & paste across

[jimapos]

Thanks Samba_ravi
it works!!
How i mark this as solved ?

[kvsrinivasamurthy]

In I$ then copied to other ranges.
But how that total remains same in M column as required by you.

Please Login or Register  to view this content.

[jimapos]

Thank you all

[Pete_UK]

... How i mark this as solved ? ...

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 this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[jimapos]

Hello
i need this final touch for my index problem Can you help me ?

[canapone]

Hi,

in M3 to be copied down (same formula of K3)


=IF(ROW($A1)<=SUM($D$4:$D$35),INDEX(E$4:E$35,MATCH(ROW($A1)-1,INDEX(SUBTOTAL(9,OFFSET(D$3,(ROW($4:$35)-4)/(ROW($4:$35)-3),,ROW($4:$35)-3;1)),))),"")

In N3, to be copied across in N3:P35

=IFERROR(INDEX(F$4:F$10,MATCH($K4&$M4,INDEX($C$4:$C$10&$E$4:$E$10,),0)),"")

Please check if yellow outputs in column N and O are what expected.

I'm quite sure there are better approach of first formula I've shared (Index bla, bla... subtotal)

Ciao

[Bo_Ry]

Another one

K4:P4 and copy down

=IF(ROWS(K$4:K4)>SUM($D$4:$D$10),"",IF(COLUMNS($K4:K4)=2,1,INDEX(C:C,AGGREGATE(15,6,ROW($D$4:$D$10)/($D$4:$D$10>=COLUMN($A$1:$AE$1)),ROWS(K$4:K4)))))

[jimapos]

Thanks canapone
you are a life saver !!!

[jimapos]

Thanks Bo_Ry
This works too..

[jimapos]

I cant figure out the final index ( ? ) i need to complete my goal.
Anybody ?