Extract columns and create separate sheet based on column criteria

[fbacchus]

Hi,
I have a spreadsheet with the following data:

Order Number In production Cutting Machinist Casualty Analyst Status Date
A100100 01/09/2015 03/09/2015 05/09/2015 John M Cutting 02/09/2015
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 05/09/2015 Jack S Machinist 02/09/2015
A100200 01/09/2015 02/09/2015 05/09/2015 Mary T In Production 01/09/2015
A100200 01/09/2015 John M Query 09/09/2015
A100300 01/09/2015 02/09/2015 03/09/2015 Frank B In production 01/09/2015
A100300 01/09/2015 02/09/2015 02/09/2015 Jack S

I would like to create separate sheets for each individual based on their name (Analyst) and with some of the columns included in their sheet. So, my first result would be:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 03/09/2015 John M Cutting 02/09/2015
A100200 01/09/2015 John M Query 09/09/2015

My Second sheet with be like this:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 02/09/2015 Mary T In Production 01/09/2015

 

[DocAElstein]

Hi Frank

I did a similar code in a Thread to this just now. With a small modification and applying directly to the Test data for you which I initially suggested in Post # 4, then I obtain the following:.

Using Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100200	01.09.2015	02.09.2015	05.09.2015			Mary T	In Production	01.09.2015	Run on 15.03.2016 12:12 PM

Mary T

_... If you clear up your exact requirement then I expect it would with minor mods give exactly what you want.

Alan

 

[fbacchus]

Alan:

Sorry for the long delay in responding but I was getting my belt under the tol (so to speak). I thing I've got it. I am posting 2 spreadsheets (input & expected output) below. Here is the sheet with the input data rows:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Order Number	In Production	Cutting	Production Date	Machinist	Casualty	Analyst	Status	Date
2	A100100	1/9/2015	3/9/2015	5/9/2015	9/1/2016	1/6/2016	John M	Cutting	2/9/2015
3	A100100	1/10/2015	2/9/2015	5/10/2015	9/2/2016	2/9/2015	Mary T	Casualty	5/9/2015
4	A100200	1/11/2015	5/9/2015	5/11/2015	5/9/2015	2/10/2015	Jack S	Machinist	2/9/2015
5	A100200	1/13/2015	2/10/2015	5/13/2015	9/16/2016	2/12/2015	John M	Query	9/9/2015
6	A100200	1/12/2015	2/9/2015	5/12/2015	9/15/2016	2/11/2015	Mary T	In Production	1/9/2015
7	A100300	1/14/2015	2/9/2015	5/14/2015	9/17/2016	2/13/2015	Frank B	In Production	1/9/2015
8	A100300	1/15/2015	2/10/2015	5/15/2015	9/18/2016	2/14/2015	Jack S	Testing	1/10/2015
All_Risks

Desired Output rows: Select those rows where "Analyst" is "Mary T" or "Frank B" and "Status" is "In Production" Here expected output including a date stamp in " Last Updated". The code is the same you provided with specific columns being selected:

￿￿

Look like sometime went haywire with the HTML Generator (thus the two squares) but the results would only be the 2 rows where "Analyst" is "Mary T" or "Frank B" and "Status" is "In Production".

Thanks

frank

 

[DocAElstein]

Hi Frank

Thanks for trying the tools. It makes it a lot clearer.

.........
Look like sometime went haywire with the HTML Generator (thus the two squares) ...........

I think I can wrap this one up very quickly for you. But as you saw the “After” did not come out !! So I am still having to “Guess” the columns and exact output you want.
Rather than me posting until I get an output that suits, please try again. But as i suggested, if you are new to the tools, then please test them out first in the Test Area .
Test Here
– You can start a post there ( Title it just “Test” or “Test Table”. ) You can reply as much as you like then to your own Thread until you get it right. ( Those Posts in the Test area are mostly ignored and deleted after about a week),. So you can practice as much as you want. !!
I do not think there is a problem with the HTML Maker. You could also try Rory’s Tools, - the ADD-IN is installed similarly to the one you have successfully got up. It has both the HTML Generator you used and a BB Code generator. Or failing that ( and while you are practicing ) try to the table option. ....for all that jem , see start of my Post #10 again.

And also a question. If no analyst is “In Production“, then currently the code will give you an empty sheet with just Headings. Do you want that? Or should no sheet be made in that case?

Alan

 

[DocAElstein]

P.s. BTW. Just a bit curious / Puzzled ??
...you said “...Look like sometime went haywire with the HTML Generator (thus the two squares)…“
_ I am curious about that as I do not see the usual bit at the bottom of the Post saying You edited to add that comment….?? So how did you know, before posting, that it did not work ?