Re: Transfer Rows with Duplicate Values
Great.
Thanks a lot shknbk2.
Posts by ivandgreat
-
-
Dears,
I would like to transfer all rows that have duplicate values between 2 columns (NameA and NameB) which exist as reverse (NameB and NameA) on the same columns.
Please see attached excel file.
br,
ivan -
Re: Find the 2 column values and search in reverse in same 2 columns
Thanks skywriter..
I really appreciated your code.
Have a nice day!
-
Re: Find the 2 column values and search in reverse in same 2 columns
Thanks again, skywriter.
What i mean is, @ sheet 1
[TABLE="width: 427"]
[tr]
[td]Item
[/td]
[td]NameA
[/td]
[td]TerminalA
[/td]
[td]NameB
[/td]
[td]TerminalB
[/td]
[td]Remarks
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]352 [/TD]2137
[/td]
[td]DKJB
[/td]
[td]1
[/td]
[td]DKJA
[/td]
[td][/td]
[/tr]
[tr]
[td]
[TD="align: right"]8[/TD]58
[/td]
[td]DKLA
[/td]
[td][/td]
[td]DKLB
[/td]
[td]No duplicate
[/td]
[/tr]
[/TABLE]Then @ sheet 2
[tr]
[TABLE="width: 427"]
[td]Item
[/td]
[td]NameA
[/td]
[td]TerminalA
[/td]
[td]NameB
[/td]
[td]TerminalB
[/td]
[td]Remarks
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]1[/TD]1
[/td]
[td]DKJA
[/td]
[td]2137
[/td]
[td]DKJB
[/td]
[td][/td]
[/tr]
[/TABLE]br,
-
Re: Find the 2 column values and search in reverse in same 2 columns
Thanks a lot, skywriter, it works perfectly fine.
What if i wanted to transfer/separate all duplicated rows to sheet2 and remain only the unique values on sheet1?
br,
-
Re: Find the 2 column values and search in reverse in same 2 columns
hi skywiter,
as per my attached file and shknbk2's code, after i run the code, please filter @ colum TerminalB.
Here it shows no Duplicate @ row20 but it shows [email protected] @ row 479, same with the other one.
The duplicate should not show from Col NameA & TerminalA to Col NameB & TerminalB.
[TABLE="width: 421"]
[tr]
[td]Item
[/td]
[td]NameA
[/td]
[td]TerminalA
[/td]
[td]NameB
[/td]
[td]TerminalB
[/td]
[td]Remarks
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]20[/TD]2002
[/td]
[td]D7AB
[/td]
[td]2191
[/td]
[td]D7AA
[/td]
[td]no Duplicate
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]479[/TD]2191
[/td]
[td]D7AA
[/td]
[td]2002
[/td]
[td]D7AB
[/td]
[td][/td]
[/tr]
[tr]
[td]
[TD="align: right"]1023[/TD]2326
[/td]
[td]DLOB
[/td]
[td]4178
[/td]
[td]DLOA
[/td]
[td]no Duplicate
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]1027[/TD]4178
[/td]
[td]DLOA
[/td]
[td]2326
[/td]
[td]DLOB
[/td]
[td][/td]
[/tr]
[/TABLE]br,
-
Re: Find the 2 column values and search in reverse in same 2 columns
Dear shknbk2,
Thanks a lot.
Please see attached file.
All duplicated rows should be transferred @ sheet2 with the same table format as sheet1, same remarks as we had, mentioning from which rows it was duplicated for both sheets
br,
-
Re: Find the 2 column values and search in reverse in same 2 columns
Thanks a lot shknbk2.
I tested it on some other data, it gives an error... some was found "no duplicates" but when i check it has duplicates, and the remarks has found the duplicate.
Please see below table results.[TABLE="width: 421"]
[tr]
[td]Item
[/td]
[td]NameA
[/td]
[td]TerminalA
[/td]
[td]NameB
[/td]
[td]TerminalB
[/td]
[td]Remarks
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]1[/TD]3000
[/td]
[td]W07B
[/td]
[td]4884
[/td]
[td]W07A
[/td]
[td]no Duplicate
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]159[/TD]4884
[/td]
[td]W07A
[/td]
[td]3000
[/td]
[td]W07B
[/td]
[td][/td]
[/tr]
[tr]
[td]
[TD="align: right"]79[/TD]4841
[/td]
[td]B20B
[/td]
[td]4840
[/td]
[td]B20A
[/td]
[td][/td]
[/tr]
[tr]
[td]
[TD="align: right"]70[/TD]4840
[/td]
[td]B20A
[/td]
[td]4841
[/td]
[td]B20B
[/td]
[td]no Duplicate
[/td]
[/tr]
[/TABLE]What if i wanted to transfer the whole rows for duplicates to another sheet and remain the unique rows only.
br,
-
Hello,
Can anyone help me how to to check in vba the values for two columns (NameA and NameB) and check it on reverse columns (NameB and NameA) and highlights the duplicates if found
[TABLE="width: 200"]
[tr]
[td]Item
[/td]
[td]NameA
[/td]
[td]TerminalA
[/td]
[td]NameB
[/td]
[td]TerminalB
[/td]
[td]Remarks
[/td]
[/tr]
[tr]
[td]1
[/td]
[td]2000
[/td]
[td]XXX1
[/td]
[td]2001
[/td]
[td]XXX2
[/td]
[td][/td]
[/tr]
[tr]
[td]2
[/td]
[td]2002
[/td]
[td]YYYA
[/td]
[td]2003
[/td]
[td]YYYB
[/td]
[td][/td]
[/tr]
[tr]
[td]3
[/td]
[td]XYZ22
[/td]
[td]ABCA
[/td]
[td]XYZ11
[/td]
[td]ABCB
[/td]
[td]no Duplicate
[/td]
[/tr]
[tr]
[td]4
[/td]
[td]2001
[/td]
[td]XXX2
[/td]
[td]2000
[/td]
[td]XXX1
[/td]
[td][/td]
[/tr]
[tr]
[td]5
[/td]
[td]2003
[/td]
[td]YYYB
[/td]
[td]2002
[/td]
[td]YYYA
[/td]
[td][/td]
[/tr]
[tr]
[td]6
[/td]
[td]2000
[/td]
[td]ZZZA
[/td]
[td]2002
[/td]
[td]ZZZB
[/td]
[td]no Duplicate
[/td]
[/tr]
[/TABLE]br,
Ivan
-
-
Re: Excel VBA - Summary table fori Vacant and Occupied
There is no data in column C, it should show the data have "36 Vacant". In your code it is showing but it was repeated in 6 consecutive rows.
-
Re: Excel VBA - Summary table fori Vacant and Occupied
To jindon, it also works well to all scenario except when it is empty..
-
-
Re: Excel VBA - Summary table fori Vacant and Occupied
Thanks again, i got an error "Subscript out of range", i just create another scenario.
-
Re: Excel VBA - Summary table fori Vacant and Occupied
Thanks again, i edit again the table, but it didn't show the right output.
-
Re: Excel VBA - Summary table fori Vacant and Occupied
Dear KjBox, could you please check it again, i just edit the table, but it didn't show the right output.
Thanks.
-
Re: Excel VBA - Summary table fori Vacant and Occupied
Thanks KjBox, i just edit the table but it didn't show the right output.
-
Re: Excel VBA - Summary table fori Vacant and Occupied
Thank richdj4.
i prefer to use VBA, since i have a 2000 rows to work on, which is on different sheets.
-
Dears,
Good day! I would like to make a summary of my table using excel vba.
From attached file, i have a number of rows which specify the name/number of rooms in col B and at col C mentioned number of occupied rooms.
If the value in col C, for example show as "Occupied 2" it will take the first 2 rows and make the summary table which is shown on col E to J. if it is "Occupied 8" it will occupy the 8 rows.. and so on.
if the next rows (after occupying the number of rows mentions) is vacant, it will count the number of vacant until it reach the row with values again.
This will have a loop until the last rows in col B and create the summary table.
Thanks in advance.
br,
Ivan -
Re: Summarized Duplication - 15USD
Dear Krishnakumar,
Can you do it for 30USD?
br,
Ivan