Re: Summarized Duplication - 15USD
Yes. i already paid 10%.
Re: Summarized Duplication - 15USD
Yes. i already paid 10%.
Dears,
Good day! I want to have a vba that will summarize my table with duplication.
Attached here is my table.
It should start to check the data in col A , if it has duplicate or not.
If found duplicate it will compare first the value in col I (status) then check if any different values in col C to H.
- If all having the same value in col I then check duplicate in col C to H, if all the same update col J, leave blank the 1st row, then put a remark value on the 2nd row "This is duplicate data"
- Update col K (Merge), concatenate value in col B, C and I, and add rank number. Leave blank if it is totally the same value on each col I, C to H
(Please see example row 2 and 3 from example table)
If no duplicate found, update col I, count the value in col I (Remarks) and update col K (Merge), concatenate value in col B, C and I, and add rank number.
(Please see example row 8 and 14 from example table)
Any value found difference after checking/counting the value in col I (Status),
- If all having the same value in col I then check duplicate in col C to H, Update the col J (Remarks) with only the col that's having different value
- Update the col K (Merge), concatenate value in col B, C and I, and add rank number.
Please check sheet 2 for the output in col J (Remarks) and K (Merge)
br,
Ivan
Re: Vlookup Sample
It works fine.
Copy & Paste is a good idea, better than having the whole formula in each row.
Thanks again sir.
Re: Vlookup Sample
It works fine, thanks gn00588950
but it is also slow..
i revsed the data for your ref.
is there anything we could add to make it more faster..
What if putting only the result value not the vlookup formula? Since it also takes time when filtering.. it always process the vlookup
here is the link for sample file
https://dl.dropboxusercontent.…he%20Code_Example_v1.xlsm
Thanks again sir.
Re: Vlookup Sample
Quote from gn00588950;713807Try not to Vlookup whole Range("A:E") if you really need to perform this action by Marco.
And change:
Thanks for your help, gn00588950, But it didn't work. what if the last row is variable?
Re: Vlookup Sample
Thanks for your reply.
But doing this, for 100Thou rows will take some time in processing that's why I wanted to make it in macro.
Dears,
I have this code, it is to much slow. How can i make it a faster?
What should i edit to check only the 2nd to the last end row of column B (1st row is a title row).
Sub VlookupValue()
Dim cell as range
For each cell in Sheets("Sheet1").Range("B:B")
Cells(cell.Row, "G") = Application.Vlookup(cell,Sheets("Sheet2").Range("A:E"),5,FALSE)
Next cell
End Sub
br,
ivan
Re: 30USD - VBA Create/add rows based on criteria
Please check.. i already sent the payment to your email. thanks!
Re: 30USD - VBA Create/add rows based on criteria
i only paid the 10%, i haven't received the contact email. thanks!
Re: 30USD - VBA Create/add rows based on criteria
Please check.. already paid..
Dears,
Good day! I would like to create a table based on the Network Address on Cell B3 and C3.
On the sample, item 1, based on cell value on C3 which is /29, only get the 29 and check it on sheet3.
on sheet3, you'll find the value 29 on col C and @ col D which will tell how many rows will be added on the last row of table @ Sheet2.
From there you'll see a value of 8 which will be the start and end of the row to be added.
The first value to be added @ col B will be same as the network address on Cell B3 and C3 at sheet 1 which is "10.131.100.248",
since the value 29 having a value of "4th" @ col B @ sheet3, it will increment the value by 1 (but will not exceed the value to 255)
@ 4th value after 3 dot which is the value 248
then increment by 1 and will not be exceed by 255 until it get the number of rows needed, so it will be
10.131.100.249
10.131.100.250
10.131.100.251
...
...
...
10.131.100.255
The first will have a value of "Network" and the last will be "Broadcast" @ col D @ sheet 2.
on Item 2, it will be the same, and you'll notice it has a value of 23 @ cell C4,
The no of rows to be added will be 512 based on Sheet 3.
Then the start value will be based on "3rd" from this value "10.131.102.0", which is 102.
Then increment by 1, so it will be,
10.131.102.0
10.131.102.1
10.131.102.2
...
...
...
10.131.102.255 (since the value should not exceed 255, it should go and increment the 3rd value now by 1 and continue increment by 1 again on the 4th value),
here the number of rows created is 256 row and still need to continue until 512 since it has a value of 512 @ Sheet 3 col D based on the /23.
to continue..
10.131.103.0
10.131.103.1
10.131.103.2
...
...
...
10.131.103.255
The first will have a value of "Network" and the last will be "Broadcast" @ col D @ sheet 2.
br,
ivan
Re: Count and Rename
thanks pangolin.. it works fine..
Good day!
Please help, i wanted to have a macro that will rename the IDs @ col B that is unique and duplicate.
The rename will be add if it is 1st, 2nd, 3rd and so on...
Please see sample file.
Thanks a lot..
br,
ivan
Re: Separate Unique Values - $20
Thanks SMC.
Re: Separate Unique Values - $20
Also how this formula works, "R1C5:R[-1]C5" in your code
Re: Separate Unique Values - $20
Please check version 2 with adjacent value
Re: Separate Unique Values - $20
Hi SMC,
I try your last code in my first excel table, but it shows different output when counting the duplicate values.
br,
ivan
Re: Separate Unique Values - $20
Hope it isn't a big deal.
Thanks again SMC.
Re: Separate Unique Values - $20
Thanks smc, could you please put a remarks on duplicated values on the adjacent column.
Count it how many it times exist before deletion.
If it has 2 put 2, if it is 4 put 4.
Re: Separate Unique Values - $20
Dear SMC,
Please check, it didn't delete the duplicate..
br,
Ivan