Transfer data from Input sheet to output sheet on condition

Hello Experts,

Need you assistance in transferring data from Input sheet to Output sheet on conditions..

Please see the attached File..

Input Sheet have Column C (Values as 1,2,3) & Data from (Column E to Column U)

Now Transfer all Data from INput sheet to Output on below condition
1) If "Column C" Cell value = 1 then copy that row to output sheet under row named ("1 TeraByte") from COlumn A to Column Q

2) If "Column C" Cell value = 2 then copy that row to output sheet under row named ("1 MegaByte") from COlumn A to Column Q

3) If "Column C" Cell value = 2 then copy that row to output sheet under row named ("1 TeraByte") from COlumn A to Column Q


Initially for sample data i have inputted only 5 rows.. the data can be more hence the ouptut file layout should not undergo any change.. only the rows should be added in between the Row data header & the Subtotal...

Also the Conditions are now for value = 1,2,3.. if it grows.. iw ill change the code accordingly...

Puzzled

Hi john,

GRT it worked well on my sample sheet.. will run the same on My actual huge data earliest by tmrw & revert ..
But puzzled as to how do i remove the old data first before i run this code..
It will add the data on every run..

can we just delete the existing data from Output sheet (Not the Headers rows & Sub total row & then execute macro

Perfect ..Thanks A ton.. its working ausome on sample data..
Will check with the actual data by tomorrow & will revert

Thanks a lot JOHN !!!!

Hello John,

It Did work fwhen i added one more condition....

Before testing it on real data please let me know the addition & changes i done in code is correct or not..
As in real data i have 20 conditions..


Currently in sheet there were 3 rows..

1 TeraByte
2 MegaByte
3 TeraByte

I added one more condition..."4 TeraByte"

and did Addition in the code


I added

Please Login or Register  to view this content.

and where ever there was End(3).Row i Changed it to "End(4).Row"

Please correct me if i am wrong.

Below is the complete Code

Please Login or Register  to view this content.

Hi John

Its not working.. coz their are 9 conditions & one row added in each block as per source file..

Please check the attached file.. sorry for inconvinence

code attached in module..

Hi John,

The data is getting duplicated on every run.. its not deleting previous data..its appending

Moreoever the Row with value "Rated ID" should not be changed..
It should be always there itself after every conditioned row..

That is on Row 14, Row 28, row 37, row 46, 55, 64, 73, 82, 91 should not be moved..

Plz suggest

If i run the code..

It populates some data not all data..
and after sometime it gives error..
Type Mismatch at below line
Case Is = 1

Hello John,

I somehow got the issue why type mismatch error was coming.. i rectified it in the code you gave..

Can you please help me for below

(Hi John,

The data is getting duplicated on every run.. its not deleting previous data..its appending

Moreoever the Row with value "Rated ID" should not be changed..
It should be always there itself after every conditioned row..

That is on Row 14, Row 28, row 37, row 46, 55, 64, 73, 82, 91 should not be moved..

Plz suggest)

I would not work with a fix Template Sheet cause that will be a hell to maintain.
I would suggest to Create the Whole Sheet by Code cause the Design for every Section is always the Same.

My Approach would be sth like this.

Edit: For the Header Thingy before row 13 you could create a template and start with a copy of that

1. create a Settings Sheet with a table ID/Name where you write 1/1 TeraByte
2. Sort the Data on the input Sheet by Column C
3. Create Blank Sheet
4. grab the name from your helper Table write it to row 13 and rated ID in 14
5. Loop through the Input Sheet until id is not 1 anymore and copy the needed columns to the new sheet
6. End the section on the new Sheet with Rated ID Sub Total
7. do that for all entrys in the helper table.

I dont have so much time today but i could take a look at it tomorrow if you don't get a solution

kind regards Loki

Hello John,

I managed to make the code in working condition.. Its done.. Thank you so much for this Appreciate your efforts & suggestions...


Thanks Loki for suggestion.. Its solved with little trick...

HI John.... i had to comment some lines of code in order to make it working on my huge data...

Everything is working fine but the formulaes are not getting updated..

The Row having "Rated ID Sub Total" outputs formula as =SUM(#REF!)

can u suggest some code which will rectify only the =SUM(#REF!) and consider the range of cells values to add above it..
The formulea are in column (J, M, O, P, Q)

thank you...

cause i already done it (had nothing to do in the Train) :D
This code is more dynamic and the result looks better then your Template :D

If you use it have fun with it if not maybe it helps others :D
Put some more comments in and added sumifs Formula to the template Sheet

Edited the Code and updated the File

Please Login or Register  to view this content.

Hi John, Below is the code with some commented.. bcoz i was getting error so i had to comment the last poarts of the code..
I am successful in plotting all the data as per conditions with deletion of empty rows in it.. just left with formulaes to be plotted properly..

Please Login or Register  to view this content.

The formulea are in column (J, M, O, P, Q)
& i am getting =SUM(#REF!)


Dear Loki, thanks for the code.. your code also worked with the data in Input sheet.. but when i actually put all original Input data in sheet which is more than 10000 it does not pick all correct data in Output sheet.. & i have to keep the format consistent coz the sheet is just one part of macro.. more macro gets executed on it.. so cant move or delete anything unless necessary.. too much of confusion on the sheet.. coz fata is around 20,000 to 50,000

Hi Loki,

The code you posted worked perfectly on huge data too.. thanks for it

John thanks too you too.. because your code too helped me built different output sheets

Thanks a Ton experts !!!!!

Originally Posted by LordLoki

cause i already done it (had nothing to do in the Train) :D
This code is more dynamic and the result looks better then your Template :D

If you use it have fun with it if not maybe it helps others :D
Put some more comments in and added sumifs Formula to the template


Sheet

Edited the Code and updated the File

Please Login or Register  to view this content.

Hi Loki,


I Sorted Settings sheet before running Macro..

It executes well IF column have Only Integers as (1,2,3,4,5,6..)
or column have only Characters values as (A,B,CC,D,F,E,R,MM)


But for IF columns have values Numbers+ Characters+AplhaNumeric as (0,A1,A2,A3,NA,NR,WR,AA1,AA2,AA3,AAA,BA1,BAA1,BAA2,BAA3) it only populates data for A1, A2, A3 rests it fails & do not popualte anything after that... Please suggest

Hi Loki, The issue remains same... .. It does not capture the Last sub total..
Moreover it populate wrong data for first conditions

If you execute this...
We only get data for A1, A2, A3.. thats it.. i am puzzled what is stopping other to populate

If this works.. i am done

Hi Loki,

The order of the data is not that important as of now.. More over if you can make everything in Uppercase even that will do
Moreover if we add a new condition in the Settings sheet the subtotal does not gets captured for last condition...

Please suggest Thanks You

Hello Loki, Even though if Sub total is not plotted that will also do.. just need the data to be in for each conditions..

Hi Loki,

I debugged, rectified it.. Bingo..

Its Bang on Target... worked superbly.. Would write a seperate code to delete the Backup Input sheet.. as it will be no longer required..

Thanks a Ton Loki

You are welcome always happy if i can help

for the backup sheet you can just not generate it at all then you don't have to delete it :D
its just a backup the code does not refer to it at all.

greets
Loki