SOLVED

Sorting a Master Spreadsheet into worksheets if cell in sortable column contains (value)

Copper Contributor

Hello, I am new to working with VBA/Macros generally, and working in Excel 2204 I am looking to create a Master Spreadsheet that automatically will populate existing AND newly entered data into worksheets sheets named by the keywords contained in a Column A.  I am thinking this function will look something like "If Cell in Column A contains "A", copy the whole row to either an existing worksheet named "A" OR automatically generate a new worksheet "A." AND "If Cell in Column A contains "B", copy the whole row to either an existing worksheet named "B" OR automatically generate a new worksheet "B," and so-on with other elements like "C" contained in Column A. I would also like this sheet to refresh automatically, whenever someone changes the master sheet. I would also hope that the data does not become duplicated, and that the first row would be copied onto all worksheets. Any guidance you might be able to offer on this would be most appreciated. 

 

Sort ByInfo 1Info 2Info 3Info 4
111 A 1111111
22 A 22222222
33333 B 3333333
A 444 44444444
5 BB 55555555
6BBBB 66666666
7777B77777777
88 CC88888888
999 C99999999

 

I would want this to look like: on three DIFFERENT worksheets, all within the same workbook. I would also like to be able to add an entry to the above table, where it would populate automatically to the below worksheets without requiring running a Macro or other type of manual refresh.  

 

Sort ByInfo 1Info 2Info 3Info 4
111 A 1111111
22 A 22222222
A 444 44444444
     
Sort ByInfo 1Info 2Info 3Info 4
33333 B 3333333
5 BB 55555555
6BBBB 66666666
7777B77777777
     
Sort ByInfo 1Info 2Info 3Info 4
88 CC88888888
999 C99999999

 

7 Replies

@cberlin216 

You can pull this off with FILTER.

 

Please see attached workbook sample.

Hi Patrick, and thank you for your response. This is very helpful for cases where Column A has a limited number of unique values. Unfortunately, my data set has extra information in addition to the filter, within the same cell, as represented by the digits 1-9 in Column A above. I am hoping to find a way that this automatic population would happen if Column A "contains" value X, rather than "matches" value X. Is there an edit I can make to the filter, or would this require macros?

best response confirmed by cberlin216 (Copper Contributor)
Solution

@cberlin216 

I've revised the sample so exact matching is not required.

Patrick, this is exactly what I was looking for & I'm even happier that it doesn't require messing with Macros. Thank you, kindly!
Patrick, is there any way to retain the height of the row when making this transfer?
Unfortunately not with a formula. You'd have to use Auto-Fit row height to adjust the row heights to display the text.
1 best response

Accepted Solutions
best response confirmed by cberlin216 (Copper Contributor)
Solution

@cberlin216 

I've revised the sample so exact matching is not required.

View solution in original post