Solved: Can U Move Whole Rows or Just Cells?

[MurcksPoms]

Ok this is what I am trying to do... I have been working with it but I keep getting errors... :doh:

I have data on a worksheet... it is seperated into columns.
On another worksheet the data is seperated into columns, also.
The columns don't match exactly because on the first page there are #s in column "a" where there aren't in the second page. (first page is numbered) Other than that difference the data is seperated the same on both pages.


I am at a loss as to how to even go about doing this... I tried several ways but no luck...

I am trying to move the data on the latter page to the first page underneath my current data, but not just a cell, the whole row at a time...

Thanks for any advice in advance!!!!

[unmarkedhelicopter]

Select the row you want to copy, click on the number beside the row, riight click, copy.
Move to other sheet, where you want the row, right click, paste, then insert a cell to make the columns line up.

[charlesa920]

I strugled with just such a situation. I was getting new data via online downloads which was added to an existing workbook as a new sheet names with the month & year.

Next the data was copied and pasted to the "database" sheet which had all the information. Pretty straight forward, record a macro and play with the 'relative references'.


But they changed the layout of the download...crap!

so now I have to matchup the new columns with the original column and it is worse because they also changed the format of some of the column.

But again I was able to record a macro and use it as a starting point...

Is this similiar to your situation?

[MurcksPoms]

Yes, I am having the same problem as you were!! I could copy and paste but it overwrties what I already have in colmun a of the first page... I need to find a way to do it without losing that column... and keep my formatting!! Thanks so much!

[mikerickson]

Would hiding the first column and ignoring what the download put there help?

[MurcksPoms]

Hi! I tried that and it pastes in the hidden cell anyways... :banghead:

[charlesa920]

Hey if you need my help you are really in trouble. I only learned how to do this because I absoutly had to!

I got some help on the forums, recorded a macro. Then I studied the macro to see what commands related to my actions.

record you macro and post it... surley somebody will be able to help
Charles

[gwkenny]

Still a little confused about what you are doing, but it sounds like you want to copy over data from one sheet to another, but you don't want the cells in column A written over.

Go to the spreadsheet that has the data you want to copy from.

Go to the first cell to copy (B1). Hold the shift key and press the End key then the Home key.

This will highlight all the cells from B1 to the last cell used in your sheet.

Copy.

Go to the sheet you want to copy to. Click on B1 and choose Paste.

You can automate this with VBA as well, but I'm hesitant to write anything when I'm still unclear of the problem.

Good luck!

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[MurcksPoms]

Ok, here is what I am trying to do...
I have a website that uses .dbw file to keep up with my dog's pedigrees. Everything must be in a certain format: number|name|sire #|dam #etc.
Without Excel a LOT of typing...
From AKC you can download 5 generation pedigrees where everything is in a table with dog's name, then sire on top, dam bottom, so on and so on. (64 dogs total)
I found that if you copy all the tables and paste them into word, then merge the cells they always end up in the same order - beautiful! The only thing was there was no seperation from one name to the next:
CH CASTILE SOUTHERN COMFORT
ORANGE
TR2000302
CH MILLAMORS GEM
RED
TR12345678
So I manually seperated all dogs by five lines and then added them to a worksheet. I used another worksheet to link to the info but seperate it in the proper format. (So that I can change the info on WebEntry at whim and the new page will format for me)
But here is my problem... some of the dogs that are listed here are already listed in my Base worksheet. That can't be because each dog must be only listed once so they can be assigned a number so that the dbw file can tell who is who's parents.
I want to move all the dogs from this sheet to the base sheet but if they are already listed then just disregard them. The only difference in this sheet and the base sheet are the numbers in column a. I actually found a way to do it but it is very long and I know there is an easier way!
I can post the workbook if ness. I hope that kind of makes a little more sense!! Thanks so much!

[mikerickson]

Hi! I tried that and it pastes in the hidden cell anyways... :banghead:
Exactly my point; they're hidden. Do you care what's in them?

[XLGibbs]

If you have hidden rows and need to copy only the visible ones, you can use

Edit>Goto>special cells>visible cells only. It will highlight only the visible rows and you can copy/paste them to another sheet without copying the hidden rows.

[charlesa920]

Ok, ...
have a website that uses .dbw

I don't think I know that extension... can it be opened directly with Excel? Access? Word?

Charles

[XLGibbs]

Ok, here is what I am trying to do...
I have a website that uses .dbw file to keep up with my dog's pedigrees. Everything must be in a certain format: number|name|sire #|dam #etc.
Without Excel a LOT of typing...
From AKC you can download 5 generation pedigrees where everything is in a table with dog's name, then sire on top, dam bottom, so on and so on. (64 dogs total)
I found that if you copy all the tables and paste them into word, then merge the cells they always end up in the same order - beautiful! The only thing was there was no seperation from one name to the next:
CH CASTILE SOUTHERN COMFORT
ORANGE
TR2000302
CH MILLAMORS GEM
RED
TR12345678
So I manually seperated all dogs by five lines and then added them to a worksheet. I used another worksheet to link to the info but seperate it in the proper format. (So that I can change the info on WebEntry at whim and the new page will format for me)
But here is my problem... some of the dogs that are listed here are already listed in my Base worksheet. That can't be because each dog must be only listed once so they can be assigned a number so that the dbw file can tell who is who's parents.
I want to move all the dogs from this sheet to the base sheet but if they are already listed then just disregard them. The only difference in this sheet and the base sheet are the numbers in column a. I actually found a way to do it but it is very long and I know there is an easier way!
I can post the workbook if ness. I hope that kind of makes a little more sense!! Thanks so much!


Sounds like a ton of work. I have also never heard of a .dbw file extension, but if you post the excel workbook and the raw data in its pasted format...I am sure a solution can be found to save you some of the manual work.

[MurcksPoms]

Good Morning!
About the .dbw thing:
A .dbw file (basicly a database file) can be opened with excel but when I first started this workbook I didn't even have a .dbw file started. It is easiest opened/edited with notepad.
Here is a pretty big chunk of it to show what I am trying to accomplish...

#|NAME|BIRTHDATE|SIRE|DAME|PHOTOLOCALE|TITLE|REGNUM|COLOR|SEX|AWARD|sOURCE| URL
^^That is the general format for the .dbw file and here are the first few entries...
1|A Little Chip Of Shirwood||||||TB016932|ORANGE|M||Source = AKC|
2|Adkins Cavalier Flopsie||||||TA751022|ORANGE|F||Source = AKC|
3|Adkins Choc Cocoa Of Poobear||4|86|||TB652069|CHOCOLATE|M||Source = AKC|
4|Adkins Chocolate Poo Bear||||||TB564260|CHOCOLATE|M||Source = AKC|
5|Adkins Sables Beaver Doll||||||TC301346|BROWN|F||Source = AKC|
6|Allayn's Gift Of Sable||329|348|||TR106577/01|ORANGE SABLE|F||Source = AKC|
7|Allayn's Zsa Zsa Gabor||111|6|||TR341143/02|ORANGE SABLE|F||Source = AKC|
8|Ammons' Smokey||18|194|||TC362671|BLACK|M||Source = AKC|
9|Anadors Ashley Olivia||||||TC314059|BLACK|F||Source = AKC|
10|Anadors Silver N Smoke|||||Ch.|TB890313|WOLF SABLE|M||Source = AKC|
So you see that I would have to do a ton of typing and figuring out which number is the sire to what dog, etc. If you want an example of the .dbw in action go here... www.murckspoms.com/search.html (http://www.murckspoms.com/search.html) Use slow search and put in Castile's Southern Comfort. That is my dog. Click pedigree with links and you will see how the file is converted to a pedigree.

Ok, about the problem at hand...
I would post the whole workbook but the forum won't allow it to post, as it is HUGE! But I will post the page I am trying to copy from and the page I am trying to copy to. Then it might be a bit more clear as to what I am trying to accomplish. I really appreciate you guys trying to help me with this!~!!

7521

The pages in question are "FormatWeb" and "Base"
Webentry is where "formatWeb" gets it's data so it came for the ride, too.
(That is the page I was talking about spacing the dog names in word from the downloadable pedigrees)

What I need to do it get all unique entries from FormatWeb (that are not already in base) to Base at the bottom and not delete the numbers listed in column "A" (which I kinda erased to make the file smaller to upload :whistle: oops)

Here is how Base and Format Web are setup:
Base:
number; name; sire; sirenumber; dam; damnumber; title; reg#; color; sex
Format Web:
same but without "number"

Thanks again everyone!!!! :hi:

[charlesa920]

#|NAME|BIRTHDATE|SIRE|DAME|PHOTOLOCALE|TITLE|REGNUM|COLOR|SEX|AWARD|sOURCE| URL
^^That is the general format for the .dbw file and here are the first few entries...
1|A Little Chip Of Shirwood||||||TB016932|ORANGE|M||Source = AKC|
2|Adkins Cavalier Flopsie||||||TA751022|ORANGE|F||Source = AKC|



This database is "delimited" but it is using a special caracter to denote the tab stop. Can you open the one off the web (.dwb) with Excel?

I don't know how to tell excel that the delimiting character is a | (I think it is called a bang).

ALSO: How many dogs do you intend to keep in this workbook?

[MurcksPoms]

I could probably just email the .dbw file to you, I don't know of any way to download it from the net but a copy is on my computer.

As far as numbers, a lot!!! I hope to keep about 30K dogs in this database... a friend of mine has about 25K in hers... as far as I know she just types them out... I'm too lazy for all of that, lol. Not so much lazy but more work smarter not harder! :thumb

[gwkenny]

What I need to do it get all unique entries from FormatWeb (that are not already in base) to Base at the bottom and not delete the numbers listed in column "A" (which I kinda erased to make the file smaller to upload :whistle: oops)

Here is how Base and Format Web are setup:
Base:
number; name; sire; sirenumber; dam; damnumber; title; reg#; color; sex
Format Web:
same but without "number"


What makes this incredibly frustrating/confusing to help you is that the description of the problem is still unclear.

On the one hand you say you deleted the numbers listed in column "A" (the dog number) from FormatWeb to save space.

Then you describe FormatWeb's setup as having no "number".

Does Format normally have the dog's "number"?

Is the problem identifying what dogs are in FormatWeb but not in Base using the dog's "number" to determine duplicate records and then appending those specific dogs to Base?

Is the problem identifying what dogs are in FormatWeb but not in Base utilizing the the dog's "name" to determine duplicate records and then appending those specific dogs to Base and assigning a dog's "number"?

Is this something that happens infrequently so a Standard Operating Procedure can be created?

Is this something you want to do often where the time spent writing code is worthwhile?

I can easily describe/create a solution, but without understanding the problem clearly, it is really frustrating/hard to help.

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[MurcksPoms]

I guess I have been confusing myself, too! lol :omg2:
I am sorry I haven't been too clear...

I deleted the excess numbers out of the "Base" page to save space. I had it numbered to 10,000. There have never been any numbers in the "format web" page.



Is the problem identifying what dogs are in FormatWeb but not in Base utilizing the the dog's "name" to determine duplicate records and then appending those specific dogs to Base and assigning a dog's "number"?


That is what I am trying to do... the dogs in "Base" have been sorted, are individual and permanent. "Base" can only be added to, never taken from.
The dogs in "FormatWeb" have just come into the workbook and need to be sorted through to identify if they are already in "Base" and if not, added to "Base" and assigned a number.

The problem lies in that not just the cell containing the dog's name needs to move, but the whole row must move also, as to move not just the dog's name but it's infomation, also. I could copy and paste the rows into Base but since Base has one extra column, (A, the one with #s), none of the information lines up. It is all off by one column.

I would be adding dogs all of the time so it isn't just a one time addition. It would be a very frequent affair.

Thanks so much for trying to help me and I am so very sorry about all the confusion, etc!!!!!

[gwkenny]

The simple way would be to insert a new column A in FormatWeb, then when you copy your data over, the columns will align.

You also need to determine if the dog in FormatWeb is already entered into Base. Are the dog's name unique? Can you use this field to search BASE to see if it already entered? If you can do that then in the new column A you just inserted into FormatWeb, I'd enter a formula that does an exact search lookup in Base of the dog's name from FormatWeb. If the name is found have the formula return a 1, else return a 0.

Then you can sort on column A and all the dogs you need to append to Base will be on top. This way you can determine which dogs you need to append rather quickly, and just copy and append them appropriately.

If this sounds like what you need to do, please let us know. Then we can work on setting up a standard routine for you.

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[charlesa920]

I saw your previous topic on getting a formula to fill in some of the blanks. But I still don't get the "BIG PICTURE" of what you are doing, or how you intend to use this data. If I don't have a good map I may not get where I'm going.

I think you should consider moving this project from Excel to Access. The limit of 65,000 rows may become a problem with the shear amount of data you intend to keep.

[figment]

give this a try

Sub adddogs()
Dim endoweb As Long, endobase As Long
Dim dogreg As Range
Dim setingsave As Variant
setingsave = Application.Calculation
Application.Calculation = xlCalculationManual
With Worksheets("BASE")
endobase = .Range("A1").End(xlDown).Row + 1
For endoweb = 1 To Worksheets("FormatWeb").Range("G1").End(xlDown).Row
Set dogreg = .Range("H:H").Find(Worksheets("FormatWeb").Range("G" & endoweb), LookIn:=xlValues, LookAt:=xlWhole)
If dogreg Is Nothing Then
Worksheets("FormatWeb").Range("A" & endoweb & ":I" & endoweb).Copy
.Range("B" & endobase).PasteSpecial (xlPasteValues)
.Range("A" & endobase) = endobase & "|"
.Range("D" & endobase).FormulaR1C1 = .Range("D" & endobase - 1).FormulaR1C1
.Range("F" & endobase).FormulaR1C1 = .Range("F" & endobase - 1).FormulaR1C1
endobase = endobase + 1
End If
Next
End With
Application.Calculation = setingsave
Application.Calculate
End Sub

it runs of the registy numbers because i figured that they were the lest likely to have duplicates.

[MurcksPoms]

The big picture is creating the .dbw file I mentioned earlier by obtaining dogs from AKC online pedigrees and putting them in the .dbw file's format.

The small picture is getting the info listed in FormatWeb to join the info in Base without creating duplicates of what is already there...

The purpose of Webentry and FormatWeb was to help me convert an AKC downloadable pedigree into the format as seen on the Base page. You can see an AKC ped here, http://nicuploads.akc.org/reg/sample_online_pedigree.cfm

Here is the process I have been using for getting all of this done...
To get the online ped in order I copy it and merge cells in Word. I then take the remaining info and make sure every dog name in exactly 5 lines apart. I then paste that into Webentry. FormatWeb picks up the info in Webentry and puts it in order for me.
I created two pages, compare1 and compare2. I have been pasting all the dogs from base, column b, into compare1. I have then been pasting all the dogs from format web, column A into compare2. I found on the cool tools page a program addin called Duplicate Master that I will run on "delete" mode targeting only compare1 and compare2 and it deletes out the duplicates on compare2. I then take all the info from FormatWeb (columns a-i) and paste it beside the remaining dogs on compare2 so that they line up. I then select and delete the rows that are blanks in column A, (that Duplicate Master deleted). After that I delete any columns that are to the left of the dogs I have remaining. I then go to a new page and paste columns b-j of Base (everything except the numbers) and at the bottom the remaining info from compare 2 and select all that, sort ascending and paste back into Base starting at column B.
I had just hoped there was an easier way that would just take the dogs from FormatWeb, decide if they were in Base already and if not put them there starting at column B.

An issue that arose is the fact that beside every dog's name is their sire and dam's name. If you try to run the Duplicate Master on only the Base page and the FormatWeb page it picks those dogs up and says there are matches already. Thus the need for the new pages, compare 1 and compare 2.

I tried running the macro from Figment, (thank you!), but it didn't do what I expected it to so it may be targeting something that I am not trying to do.


Thanks so much!!!!!!!

[charlesa920]

Good Morning!
an example of the .dbw in action go here... www.murckspoms.com/search.html (http://www.murckspoms.com/search.html) Use slow search and put in Castile's Southern Comfort.

Okay so now I understand what I see when I do the above.

But I could not get it to give me a "download". Is this part of your paid account?

please attach a sample of one of the downloads in its original form before you do anything to it...

perhaps if I see the raw data it will help...

[gwkenny]

Well, we are almost there with the problem definition.


I had just hoped there was an easier way that would just take the dogs from FormatWeb, decide if they were in Base already and if not put them there starting at column B.

Remember I asked previously, "Is the problem identifying what dogs are in FormatWeb but not in Base utilizing the the dog's "name" to determine duplicate records and then appending those specific dogs to Base and assigning a dog's "number"?"

From reading your messages, I gather that's the problem.

This IS NOT hard. We just needed to be clear on the problem so we can give you a solution. We are 20+ posts in this thread and we've just defined the problem.

Sorry to rant.

Here is a relatively manual procedure you can follow. I just don't want to invest any time in coding until I know what type of deliverable gives an adequate solution.

GIVEN
The BASE and FormatWeb worksheets in the same format you've given in your upload Database22.xls

STEPS
A) Determine if dog in FormatWeb is in Base
A1) Insert a new column at the beginning of FormatWeb. You now have a blank Column A and the column structure is the same as BASE

A2) Insert this formula in cell A1 of FormatWeb: =COUNTIF(BASE!B:B,FormatWeb!B1)

What this formula does is returns a 1 if the dog is found in BASE and a returns a 0 if the dog is new

A3) Copy this formula from A1 down for all the rows of data in FormatWeb

NOTE: This may take some time to calculate as it searchs the ENTIRE column in BASE. Instead, you may change the address from "BASE!B:B" to "BASE!$B$1:$b$40000" or whatever row you think is appropriate

B) Isolate only the new dogs that should be appended to base

B1) Copy the entire data from FormatWeb into a new worksheet (call it NewDogs). You do this be highlighting the worksheet FormatWeb and then going to NewDogs and choose the menu EDIT/PASTE SPECIAL and in the dialog box, under PASTE choose VALUES

B2) Sort the data in NewDogs based on Column A in Ascending order. All the new dogs (with a value of 0 in column A) will now be on the top.

B3) Go through and delete all the dogs with the number 1 in Column A. These dogs are already in base

B4) Erase the contents of column A

C) Append data and finish
C1) Copy the data from NewDogs to the bottom of the data in BASE
C2) Copy down your formulas in column D and F
C3) Assign Dog Numbers to the new dogs you just appended

DONE

Does this manual procedure do what you want?

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[figment]

I tried running the macro from Figment, (thank you!), but it didn't do what I expected it to so it may be targeting something that I am not trying to do.

the macro should have gone through every dog in the FormatWeb sheet, compared its registry number to the registry numbers on the BASE sheet, if the number didn't exist then it should have copyed the dogs information to the BASE sheet. i dont know much about dog pedigries but i assmumed that the names might duplicate, but the regestry numbers never would.

[MurcksPoms]

Figment: I just tried the vba code again and I don't know how I screwed it up the first time but it worked like a charm!!! It did exactly what I needed it to! Beautiful job! :clap:
I am most certainly here: :cloud9:

Thank you everyone for all of your help! It was so very much appreciated!!!!!! To everyone's relief I am going to mark this thread solved!!! Merry Christmas! :xmas: