How to highlight/select the line items in detail for same item available in cumulative no

[ceenivas]

Hi,

I have two sheets in a file. Sheet 1 is a cumulative number of certain part codes and Sheet 2 is detailed sales of same part codes consist of multiple quantities. I am looking for an option wherein from the cumulative number of a product to be highlighted or selected from the detailed list till the total quantity matches to the cumulative numbers. Ex : Cumulative figure of Part A is 50 Nos. which was sold against multiple invoices with multiple numbers ( which could be more or less than 50)., now i need to highlight the Invoices with multiple line items till it reaches 50 Qty.

[kersplash]

Can you attach a sample workbook showing your source data and mock-up what you are trying to achieve.

Go Advanced -> Manage Attachments -> Upload

[ceenivas]

Hi,
File Attached

[kersplash]

No it's not.

[ceenivas]

Attaching the file again. Can you please check

[kersplash]

So can you show for the sample data you've provided what you would like your output to display (for a small sample). Mock it up.

[ceenivas]

Please take an example of Part code C11CC83412, cumulative figure is 444, actual sale is 1791 Nos.(Refer Detailed sheet) consist of 141 rows with multiple quantities. Here what out put i am looking at is , consider up to 46 rows wherein the sale qty is 441, next row is 10 Nos. out of which 3 Nos only to be considered to match with cumulative figure of 444, rest of the invoices not to be considered., probably another column can be added to arrived till 444 Nos.

[kersplash]

Better if you can show in your attached workbook the output.

[ceenivas]

Please refer column E, wherein i highlighted the actual eligible line items which is 444 (as per cumulative), up to 441 we can consider the full qty., next row it is 10 Nos wherein we need to consider the eligible qty as 3 only so as to match with 444. Hope this meets your requirement for working

[ceenivas]

i am looking at similar display for all part codes which matches the cumulative figures. In sample file shared with you, i have manually put the eligible Qty., I am looking at a formula which consider the eligible line items for all PART Codes as per cumulative numbers given

[ceenivas]

Hope my requirement is clear now and it will be great if you can provide the solution for the same

[ceenivas]

Hi.,
Any update on my requirement

[JeteMc]

This proposed solution employs two helper columns which may be moved and/or hidden for aesthetic purposes.
Column F is populated using:

Please Login or Register  to view this content.

Column G is populated using:

Please Login or Register  to view this content.

Column E is populated using: =SUM(F2:G2)
The conditional formatting formula is: =$D2=$F2
Let us know if you have any questions.