EXCEL - Copy and Paste Duplicates

[1A_OS]

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!

[Flarebrass]

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:

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%
}

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.

[1A_OS]

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

[Flarebrass]

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
}

I think that should do what you're looking for. Now for someone with COM and Excel experience to turn that into actual AHK code . Sorry I can't be of more help.

[AlphaBravo]

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]

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

Thanks it worked!