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?
4 Replies
To dynamically segregate your orders data into separate sheets based on the segment in Column H—without using a macro—you can use Excel’s FILTER function (available in Excel 365 and Excel 2019). In each individual sheet (e.g., Consumer, Corporate, Home Office), use a formula like =FILTER(Sheet1!A:Z, Sheet1!H:H="Consumer") to pull only relevant rows. This way, any updates in Sheet 1 will automatically reflect in the segmented sheets. It’s a clean, formula-based solution—much like organizing travel data from ahttps://carridearabia.com/into separate routes: no macros needed, just smart filtering!
Hi m_tarler thanks for this, but my requirement is the data should be dynamically changeable. if i make any updates or changes to the data in orders sheet related to segregated data sheets they also should be updated. when used the sample which you have attached when i made any change (delete a cell or overwrite) it is throwing as #NAME?. could you please help on this.
if you are getting a #NAME? that means excel isn't recognizing something in the formula.
a) do you have excel365? if you type =FILT do you see an autocomplete box with the option for FILTER? I suspect this is your issue. What version of Excel are you using?
b) when you edit or delete a cell that shouldn't be a problem as long as you didn't delete the Table. So in that example I referenced a 'named table' in particular "Orders" was defined as a table and then Orders[Segment] is the column of data in that Orders table with the heading 'Segment'. Even if you edit the column header from Segment to something else Excel should recognize that but if you delete the column Segment then Excel will throw an error as that column no longer exists (but probably not the #NAME? error you mentioned)
Emmarky you can do this using FILTER or a Pivot Table or PowerPivot
attached is a sample using the FILTER function
=FILTER(Orders,$A$1=Orders[Segment],$A$1&" have no data")where on each of the 3 sheets I typed the name of the Segment into A1 and on the Orders page I 'formatted as table' the data and named that table Orders so that the formula is more 'readable'
