Hello,
i'm using AHK for the first time in EXCEL and i need a lil help!
I wanna check if there are duplicates in a column, if it says yes than it should copy the information of the next column of the second duplicate and paste it into the first one.
If this isn't possible i could seperate both EXCELs!
Example
__ A __ B
1| 500 Hello
2| 200 Thanks
3| 100 Bye
4| 500 Greetings
5| 605 Welcome
Now it should copy Greetings from B4 and paste it into B1
If you provide code, pls explain it - i wanna know whats goin on, so i can use it later!
I hope you can help me!
EXCEL - Copy and Paste Duplicates
- Flarebrass
- Posts: 104
- Joined: 20 Nov 2015, 13:13
- Location: USA
- Contact:
Re: EXCEL - Copy and Paste Duplicates
This is very easily possible, and best using COM. Unfortunately, I don't have experience with COM, so I can't provide any code examples, but here's the pseudocode:
You didn't clarify quite a bit of information, so I'll have to ask. If there are multiple duplicates, do you want the final duplicate to be repeated in every matching row? Do you want the first matching row to be deleted? What do you want to do with the duplicate data?
That pseudocode will grab the last of each duplicate found and copy the B column's data into each former matching row.
Code: Select all
Build all COM objects
Read values of Column A into an array
index := 1
Loop from index to (final cell - 1):
{
secondIndex := index + A_Index
if (A%index% = A%secondIndex%)
B%index% := B%secondIndex%
}
That pseudocode will grab the last of each duplicate found and copy the B column's data into each former matching row.
(Note that I can't test my code before posting, so beware of bugs! -Flarebrass Amatzikahni)
Re: EXCEL - Copy and Paste Duplicates
If there are multiple duplicates, do you want the final duplicate to be repeated in every matching row? There are max. 2 duplicates
Do you want the first matching row to be deleted? (Yes)
What do you want to do with the duplicate data? ->
In the end i have a number in the first column and information in the second. I wanna check if the number exists twice and if so it should update the information. Higher number (so 2nd duplicate is up to date) is the latest.
After that the second duplicate should get deleted.
I really appreciate your help! Thanks
Do you want the first matching row to be deleted? (Yes)
What do you want to do with the duplicate data? ->
In the end i have a number in the first column and information in the second. I wanna check if the number exists twice and if so it should update the information. Higher number (so 2nd duplicate is up to date) is the latest.
After that the second duplicate should get deleted.
I really appreciate your help! Thanks
1A_OS
- Flarebrass
- Posts: 104
- Joined: 20 Nov 2015, 13:13
- Location: USA
- Contact:
Re: EXCEL - Copy and Paste Duplicates
Code: Select all
Build all COM objects
totalRows := (last row index, assuming the data starts at row 1 and has no breaks or blank lines)
editIndex := 1
compareIndex := 1
Loop
{
compareIndex++
if (cell A%editIndex% = cell A%compareIndex%)
{
cell B%editIndex% := cell B%compareIndex%
select and copy cells A%compareIndex+1% through B%totalRows% into cells A%compareIndex% through B%totalRows-1%
delete contents of row %totalRows%
totalRows--
compareIndex--
}
if (compareIndex = totalRows)
compareIndex := ++editIndex
if (editIndex+1 = totalRows)
break
}
(Note that I can't test my code before posting, so beware of bugs! -Flarebrass Amatzikahni)
- AlphaBravo
- Posts: 586
- Joined: 29 Sep 2013, 22:59
Re: EXCEL - Copy and Paste Duplicates
Code: Select all
Num := [], RowToDelete := []
xl := ComObjActive("Excel.Application")
for cell in xl.ActiveSheet.UsedRange.Columns(1).cells
{
if !Num[cell.text]
Num[cell.text] := cell.offset(0,1)
else
{
Num[cell.text].value := cell.offset(0,1).value
RowToDelete.push(cell.row)
}
}
for i, row in RowToDelete
xl.rows(row - A_Index+1).EntireRow.delete
Re: EXCEL - Copy and Paste Duplicates
Thanks it worked!AlphaBravo wrote:Code: Select all
Num := [], RowToDelete := [] xl := ComObjActive("Excel.Application") for cell in xl.ActiveSheet.UsedRange.Columns(1).cells { if !Num[cell.text] Num[cell.text] := cell.offset(0,1) else { Num[cell.text].value := cell.offset(0,1).value RowToDelete.push(cell.row) } } for i, row in RowToDelete xl.rows(row - A_Index+1).EntireRow.delete
1A_OS