Moving specific data into separate worksheets [Solved/Closed]

Report
-
 Steve -
Hello,


I have a list in a column as follows:
DAY TYPE PHASE
1 SUPPORT353 33
2 SUPPORT363 24
3 GENERAL372 14
4 SUPPORT474 25
5 SUPPORT171 17
6 PACKAGE354 17
7 SUPPORT535 28
8 PACKAGE255 17
9 GENERAL258 99
10 GENERAL12 16
11 PACKAGE145 61


Now the above is in WORSKHEET 1 (or tab 1)

I already have worksheet 2, 3 and 4 prepared with the same headings "DAY", "TYPE and PHASE" in place.

I actually want all the days to be displayed in each of the 3 prepared worksheets, BUT to worksheet 2 I would only like to transfer the TYPE which begins with SUPPORT. In worksheet 3 I would only like to transfer the TYPE which begins with PACKAGE. And in worksheet 4 I would only like to transfer the TYPE which begins with GENERAL.

I would also like the relevant PHASE to move with it's specified TYPE (and naturally it must fall on the correct DAY).

This is how I would envisage the three worksheets that have received the data from worksheet 1 to look like:

Worksheet 2 (SUPPORT):

DAY TYPE PHASE
1 SUPPORT353 33
2 SUPPORT363 24
3
4 SUPPORT474 25
5 SUPPORT171 17
6
7 SUPPORT535 28
8
9
10
11


Worksheet 3 (PACKAGE):

DAY TYPE PHASE
1
2
3
4
5
6 PACKAGE354 17
7
8 PACKAGE225 17
9
10
11 PACKAGE145 61


And finally Worksheet 4 (GENERAL):

DAY TYPE PHASE
1
2
3 GENERAL372 14
4
5
6
7
8
9 GENERAL258 99
10 GENERAL12 16
11



Please help me find a way, I have been trawling the internet looking for a solution but i have had no luck. Rise to the challenge and make me very happy please :)

Thanks in advance,

Steve

7 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
Steve
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Thanks for the swift response rizvisa :) This is how I forsee it, really hope you can help!!

https://authentification.site/files/21978173/ExampleDoc.xlsx

Thanks again,
steve
Just to add, the worksheet named "sheet1" is how it is originally, and i want to distribute data to the three other worksheets as shown :)
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
Steve, what is the concept of day. The most efficient way is to move all the data including day. You said that the data needs to go to the row with matching day. Is this just day of month and would be going from 1-31, one row at a time ? In that case the missing dates can be added once the data has been moved. Or is there a problem
Hi rizvisa,

I was looking around and someone suggested the following formula, which works:)

'SUPPORT' Worksheet:

Cell A2: =Sheet1!A2
Cell B2: =IF(LEFT(Sheet1!B2,7)="SUPPORT",Sheet1!B2,"")
Cell C2: =IF(LEFT(Sheet1!B2,7)="SUPPORT",Sheet1!C2,"")

'PACKAGE' Worksheet:

Cell A2: =Sheet1!A2
Cell B2: =IF(LEFT(Sheet1!B2,7)="PACKAGE",Sheet1!B2,"")
Cell C2: =IF(LEFT(Sheet1!B2,7)="PACKAGE",Sheet1!C2,"")

'GENERAL' Worksheet:

Cell A2: =Sheet1!A2
Cell B2: =IF(LEFT(Sheet1!B2,7)="GENERAL",Sheet1!B2,"")
Cell C2: =IF(LEFT(Sheet1!B2,7)="GENERAL",Sheet1!C2,"")

However, the remaining question that noone seems to be able to answer is as follows. How would I have to adjust the formula if I wanted more than one "text" transferred into one worksheet. For example, if I wanted the information regarding "PACKAGE" and "SUPPORT" to go into the worksheet named PACKAGE.

If you could give me a solution, I shall be eternally grateful :D

Steve
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
'PACKAGE' Worksheet:

Cell A2: =Sheet1!A2
Cell B2: =IF(LEFT(Sheet1!B2,7)="PACKAGE",Sheet1!B2,IF(LEFT(Sheet1!B2,7)="SUPPORT",Sheet1!B2,""))
Cell C2: =IF(LEFT(Sheet1!B2,7)="PACKAGE",Sheet1!C2,IF(LEFT(Sheet1!B2,7)="SUPPORT",Sheet1!C2,""))

How ever, I am not too sure if using the formula is the way to go. It can cause grief, during filtration, deleting or moving around rows. Since you know your requirement best, you would know if that is some thing you should be concerned about or not
Thank you Rizvisa, you are a legend ! :)