Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

can any one help on how to copy unique values of one column to another column in same worksheet #559

Closed
jampaniharish opened this issue Mar 12, 2019 · 13 comments

Comments

Projects
None yet
3 participants
@jampaniharish
Copy link

commented Mar 12, 2019

No description provided.

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

test.xlsx want to copy unique IP address from column a to column b.

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

I have tried this it copy's all the data and question is how to copy only unique values from column A to C?

$excel = Open-ExcelPackage -Path "C:\temp\test.xlsx"
$ws = $excel.Workbook.Worksheets["sheet1"]
$ws.Cells["A:A"].Copy($ws.Cells["C:c"])
Close-ExcelPackage $excel -Show

@pkarunkar

This comment has been minimized.

Copy link

commented Mar 12, 2019

I m sure there might be multiple ways to get this done.
My approach would be

$data = import-excel "C:\temp\test.xlsx" -startcolumn 1 -end column 1
$data.column1header | select-unique | export-excel "C:\temp\test.xlsx" -startcolumn 3 -show

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

Thanks for the update but select-unique is not working.

$data.column1header | select-unique | export-excel "test.xlsx" -startcolumn 3 -show
select-unique : The term 'select-unique' is not recognized as the name of a cmdlet, function, script file, or operable program. Chec
verify that the path is correct and try again.
At line:1 char:23

  • $data.column1header | select-unique | export-excel "test.xlsx" -start ...
  •                   ~~~~~~~~~~~~~
    
    • CategoryInfo : ObjectNotFound: (select-unique:String) [], CommandNotFoundException
    • FullyQualifiedErrorId : CommandNotFoundException
@pkarunkar

This comment has been minimized.

Copy link

commented Mar 12, 2019

@pkarunkar

This comment has been minimized.

Copy link

commented Mar 12, 2019

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

Thanks again for quick response,

Yes I have tried the same but no luck. I have only select-object, select-string, select-worksheet and select-xml methods when Used tab completion. I am missing something?

$data = import-excel "test.xlsx" -startcolumn 1 -endcolumn 1
$data

IP

10.36.39.3
10.36.39.11
10.36.39.88
10.39.1.101
10.39.1.101
10.39.1.101
10.39.96.72
10.39.96.72
10.39.96.72
10.36.39.3

$data.ip
10.36.39.3
10.36.39.11
10.36.39.88
10.39.1.101
10.39.1.101
10.39.1.101
10.39.96.72
10.39.96.72
10.39.96.72
10.36.39.3
$data.ip|Select-unique
Select-unique : The term 'Select-unique' is not recognized as the name of a cmdlet, function, script file, or operable program. Chec
verify that the path is correct and try again.
At line:1 char:10

  • $data.ip|Select-unique
  •      ~~~~~~~~~~~~~
    
    • CategoryInfo : ObjectNotFound: (Select-unique:String) [], CommandNotFoundException
    • FullyQualifiedErrorId : CommandNotFoundException
@pkarunkar

This comment has been minimized.

Copy link

commented Mar 12, 2019

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

Thanks a lot it worked. My bad I had read the command mistakenly as select-unique instead of select -unique (select space unique) went through the thread again and found the difference.

$data.ip|Select -unique
10.36.39.3
10.36.39.11
10.36.39.88
10.39.1.101
10.39.96.72

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

You still using select -unique without space.
I understand you get confused with verb-noun command format.

Please learn about alias.. Or use select-object -unique..

Thanks for the suggestion.

@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 12, 2019

I am new to use import excel module similarly how do we apply simple formulas on a column can you give any examples please.

fro example from the below how to get count of rows on salary column or how do we use sum function here. can you guide me to apply some formula. thanks in advance.

salary
10000
20000
30000
40000

@dfinke

This comment has been minimized.

Copy link
Owner

commented Mar 12, 2019

Please review the link

https://github.com/dfinke/ImportExcel/tree/master/Examples

=Sum

Please review the examples to see how to use Set-Format as well, in order to work with an existing Excel file.

image

$xlfile = "$env:TEMP\salary.xlsx"
rm $xlfile -ErrorAction SilentlyContinue

ConvertFrom-Csv @"
Salary, Total
100,=sum(A:A)
200
300
"@ | Export-Excel $xlfile -Show
@jampaniharish

This comment has been minimized.

Copy link
Author

commented Mar 13, 2019

Thanks dfinke and pkarunkar for your help, I just started using github very recently and this community rocks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.