Jul 04 2019 09:31 PM
Hi,
I have large data set that changes rows every 10,000. I have large number of products, and looking to find the return for each. Every 10,000 it changes year. So i want to find the return from one year to another for each product. Looking to find find the 10,000th row for each column. Eg match asset code, "case" and match sim number. So row 10,001 will find cell 1 for each product and scenario.
Attached is an example.
Greatly appreciate any help or insight.
Kind regards,
Jul 10 2019 01:27 AM - edited Jul 10 2019 01:28 AM
I had a look at your file, but it is unclear to me what you are trying to achieve precisely. Can you please include (where possible) cell addresses in your request so we can see what information needs to be looked up where?
Jul 10 2019 03:44 AM
Hi try the following:
Home->Conditional Formatting->Highlight Cell Rules->More rules->Create your rule
You can select a specific color for the row number, as a rule, and filter by color.
Or you can play around with MOD()ROW().
Hope my 1 cents worth helps you in the right direction.
Jul 10 2019 05:22 PM
Thanks for the message.
In my spreadsheet i am trying to find a cell based on the "Year", "Case Type", "stock type".
From the attached i want to find the cell highlighted Yellow. This would be cell D16. The criteria i want it to match is highlighted red, this will then we able to use the formula across the range of data.
Thank you kindly for your assistance.
Jul 10 2019 11:42 PM
Hi
I see you have Macros in the workbook? Also noticed that the first 10000th rows are a year . Why don't you break your workbook into sheets named year 1, 2 and 3 each sheet will only have 10000 rows? Might make it easier for you?
Jul 11 2019 01:17 AM
@calof1 Like in the attached? (green cells)
Jul 11 2019 01:24 AM
Hi
is it wise to use array formulas which do slow down calculations given the already big size of the data set? Wondering.
Jul 11 2019 01:57 AM
Jul 11 2019 06:28 PM
Thank you for the assistance, this works for my criteria perfectly.
Thank you again for your help.
Kind regards,
Jul 12 2019 02:42 AM