Forum Discussion
Excel data segregation
Hi All,
I have orders data in Sheet 1, and it needs to be segregated into different sheets based on the segment in Column H (e.g., Consumer, Corporate, or Home Office). Further data analysis will be conducted in the individual sheets by adding additional columns. All data should appear in the respective individual sheets. Since the data in Sheet 1 is dynamic, any changes made there should automatically reflect in the respective sheets. What would be the best approach to achieve this without using a macro?
5 Replies
Hi HansVogelaar this is working i have updated to latest version but when i delete any cells in the Orders sheet it is appearing as 0 in the other sheets, how to show blank cells from the orders sheet as blanks in the other sheets please
In the workbook that I attached to my first reply, empty cells from the Orders sheet will be blank on the other sheets too. The last part of the formula
=LET(f, FILTER(Orders!A2:X100000, Orders!H2:H100000="Consumer"), IF(f="", "", f))
takes care of that.
Hi HansVogelaar
thanks for this but my requirement is when ever i update or make any changes in the data in the main sheet(Orders) it should automatically reflect in the segregated sheet. if i use the attached version when ever i update the main sheet(Orders) i was getting as #NAME in the other sheet. could you please help on this?
Which version of Excel do you have? FILTER works in Excel in Microsoft 365, and in Office 2021 and 2024 (and in the online/browser version as well).
You can use the FILTER function. See the attached version.
