# Sort PCs in Sites

Please I need a script that gives me as a result the number of computers located in a site :
Example :
I have IN FILE EXCEL :
Scope                                     Start IP Address                 End IP Address
[12.182.1.0] SITE 1                12.182.1.11                           12.182.1.30
[12.182.8.0] SITE 2                12.182.8.130                        12.182.8.254
and I have :
PC1                12.182.1.17
PC2                12.182.8.144
PC3                12.182.1.19
PC4                 12.182.1.29
I will find the number of computers that are in the site 1 and site 2 ...
thank you very much in advance
VB ScriptPowershellMicrosoft Excel

Last Comment
redmondb

8/22/2022 - Mon
redmondb

Hi, DRRAM.

You mention a script, but it can simply be done with formulas. Please see the attached.

Regards,
Brian.
PC-s-per-Site.xlsm
DRRAM

please "redmondb" Can you explain the method
Start No.      End No.      PC's   ???
thx
DRRAM

and where I will put the @ computer IP in this file
redmondb

DRRAM,

OK, last question first - the individual PC's addresses go into the second sheet ("PC's").

On the "Sites" Sheet...
"Start No." - the Site's start IP address converted to a decimal number.
"End No."   - the Site's end IP address converted to a decimal number.
"PC's"       - the number of PC's in the site. (This formula will need to be changed to reflect the number of rows on the "PC's" sheet, please let me know roughly how many PC's you have.)

On the "PC's" Sheet...
"Work"      - the PC's IP address converted to a decimal number.

Regards,
Brian.
redmondb

DRRAM,

Please see the attached if you want to have the Site shown for each PC. Very important - the "Sites" sheet must be sorted by Column D ascending.

I'll be away for a few hours, but I'll check in as soon as I get back.

Regards,
Brian.
PC-s-per-Site-V2.xlsm
DRRAM

Redmondb please see the attached.
“This formula will need to be changed to reflect the number of rows on the "PC's" sheet”,
I have 3000 PC's but
but the number changes
is what we can change each time the number of PCs

test-PC-s-per-Site-V2.xlsm
redmondb

DRRAM,

Please see attached. I changed the formulas so that they'll now handle up to 10,000 PC's and 1,000 Sites.

Regards,
Brian.
PC-s-per-Site-V4.xlsm
wls3

Assuming you're dealing with a file C:\test\eetest.csv which contains the data above:
``````foreach(\$line in ((Import-Csv -Delimiter "," -Path C:\test\eetest.csv -Header @("Scope","Start IP Address","End IP Address")) | Select-Object -Skip 1)) {
Write-Output "\$(\$line.Scope.Split(" ")[1]) \$(\$line.Scope.Split(" ")[2]): \$(\$line."End IP Address".Split(".")[3] - \$line."Start IP Address".Split(".")[3]) IP Addresses"
}
``````
Not quite a one liner, but, close.
DRRAM

redmondb thx verry mutch I am trying it
DRRAM

wls3 please how I can test this code
wls3

You need a standard command delimited file (.csv) with the three fields in the listed above in your posts.  I took the sample file I used from your sample rows above and replaced tabs (or spaces) with commas.  Point to that file, after you have made it a .csv with command delimiters, and, it should work.  Also, I wrote in this v2.
DRRAM

redmondb  in sheet PC's, in sheet PC's you set "Match" please
DRRAM

wls3 please can you send me the csv file and how I can test
wls3

Save this to a plain text file as eetest.csv:
``````Scope,Start IP Address,End IP Address
[12.182.1.0] SITE 1,12.182.1.11,12.182.1.30
[12.182.8.0] SITE 2,12.182.8.130,12.182.8.254
``````
Create a folder on your machine named C:\test and save the file into this folder.  As written the following should work:
``````foreach(\$line in ((Import-Csv -Delimiter "," -Path C:\test\eetest.csv -Header @("Scope","Start IP Address","End IP Address")) | Select-Object -Skip 1)) {
Write-Output "\$(\$line.Scope.Split(" ")[1]) \$(\$line.Scope.Split(" ")[2]): \$(\$line."End IP Address".Split(".")[3] - \$line."Start IP Address".Split(".")[3]) IP Addresses"
}
``````
redmondb

DRRAM,

redmondb  in sheet PC's, in sheet PC's you set "Match" please

Sorry, I don't understand your question. The "Match" column returns the row (if any) in which a possible match is to be found. The "Site" column uses this value to check that it is indeed a match (and so return the Site's name) or to display "N/A" if there's no valid match.

Regards,
Brian.
DRRAM

redmondb ok verry good
but how to precise the PC's up to 10,000 PC's and 1,000
DRRAM

redmondb ok verry good
but how to precise the PC's up to 10,000 PC's and 1,000

and how to convert the @IP in decimal?? please
wls3

This link provides some functions for converting between various formats in Powershell:

http://www.indented.co.uk/index.php/2010/01/23/powershell-subnet-math/
DRRAM

wls3
how I'll run this command in the cmd????

foreach(\$line in ((Import-Csv -Delimiter "," -Path C:\test\eetest.csv -Header @("Scope","Start IP Address","End IP Address")) | Select-Object -Skip 1)) {
Write-Output "\$(\$line.Scope.Split(" ")[1]) \$(\$line.Scope.Split(" ")[2]): \$(\$line."End IP Address".Split(".")[3] - \$line."Start IP Address".Split(".")[3]) IP Addresses"
}
redmondb

DRRAM,

(1) Specify your sites in the "Sites" sheet in columns A:C. Copy the cells D2:G2 down to your last used row.
(2) Specify your PC's in the "PC's" sheet in columns A:B. Copy the cells C2:E2 down to your last used row.

That's it as long as you have less than 10,000 PC's and 1,000 sites. If you might have more then let me know.

Is this an occasional job or run that you'll be running frequently?

Thanks,
Brian.
DRRAM

I will be running frequently every week

Thanks,
DRRAM

redmondb

I will be running frequently every week

and can you explain me how to convert the @IP in decimal?? please

Thanks,
redmondb

DDRAM,

You don't have to convert the IP addresses - that's done automatically by the formulas.

Looking back at your previous questions, am I right that you don't usually use Excel? You specifically asked for a script, so I suspect that even a straightforward Excel solution is not a good match for you.

wls3
On the basis of the above, I'm not helping and so I'll step back. Please note that DDRAM is trying to identify the number of PC's per Site so you'll have to process the list of PC's as well.

Regards,
Brian.
redmondb

DDRAM,

Apologies, but I think I'm wasting your time. wls3's approach is much nearer to your needs.

Regards,
Brian.
wls3

In Windows, start an instance of Powershell.  Start | All Programs | Accessories | Windows Powershell | Powershell.  When it starts, place the command in the block above and hit enter.  If you have added the folder and file as noted it should run.  There are further issues you will encounter if you run this from a script file but that is a different issue that can be dealt with later provided we can get this going first.
DRRAM

redmondb
yes I know I don't have to convert the IP addresses - that's done automatically by the formulas. but i want to you explain me the formulas. thx
on the contrary your Excel solution is a verry good match for me. and I will running every week
I ask questions to understand the formulas
thx for your understanding
DRRAM

with the solution of "wls3" I deal the computers on the network connexion
against, I have a list of computers but from this list I have computers that do not connect in the network
"redmondb" for this your method for me is perfect
wls3

Ok.  I know these others guys did a lot of work to help out.  I just like Powershell because it is very flexible and powerful in many situations.  Hopefully you got the answer you needed which ever solution you chose.
DRRAM

redmondb
want you please to explain me the formula used for convert the IP to decimal addresses. thx
DRRAM

wls3
I'll use your method of course because they are relevant for all pcs connected and I will test tomorrow at work, and I answer.
thank you very much
DRRAM

wls3
I have one question please
Your method is limited by two sites but if I have 1000 site  and 10000 PC's how I will do change your code??
redmondb

DRRAM,

Oh, OK, glad to help. (BTW, I'm assuming that you understand IP's dotted decimal format. If you need more on it please see "Address representations" here.)

The IP conversion is quite crude (but accurate!). Also, to make it easier to read, I split the formula over a number of lines...
``````=MID(B2,1,FIND(".",B2,1)-1)*2 ^ 24
+MID(B2,FIND("#",SUBSTITUTE(B2,".","#",1),1)+1,FIND("#",SUBSTITUTE(B2,".","#",2),1)-FIND("#",SUBSTITUTE(B2,".","#",1),1)-1)*2 ^ 16
+MID(B2,FIND("#",SUBSTITUTE(B2,".","#",2),1)+1,FIND("#",SUBSTITUTE(B2,".","#",3),1)-FIND("#",SUBSTITUTE(B2,".","#",2),1)-1)*2 ^ 8
+MID(B2,FIND("#",SUBSTITUTE(B2,".","#",3),1)+1,9999)*1
``````
The first line extracts the start of the IP address, up to but excluding the first full-stop. It then multiplies this by 2^24 (= 16,777,216).

The second line extracts the data between the first and second full-stops and multiplies it by 2^16. I'm not proud of this code, but it's the cleanest way I know to do it. If you really want a detailed explanation, I'll do it, but I wouldn't recommend it!

Third and fourth lines are doing similar things with the two remaining blocks of data.

Regards,
Brian.
wls3

@DRRAM,

I didn't address the problem of the PC's, only the sites.  If you have 1 or 10000 sites, this script will process them accordingly; all you have to do is add more entries to the file.  I will reread the thread to see if I can figure out what it is that you need for the PC's portion of the script and provide a script for that part.
wls3

@DRRAM, can you explain what you need for the second portion for the second portion of the script?  From what I can tell, you are looking to see if PC's fit within ranges listed in the first data set.  Is that correct?  If it is, I assume you need to associate PC's with their SITES.  Please verify if that is correct.
DRRAM

Hello WLS3
For example I have :
[12.182.1.0] Ste1,12.182.1.13,10.128.1.30
[12.182.8.0] Ste2,12.182.8.130,10.128.8.254
[12.182.10.0] Ste3,12.182.10.115,10.128.11.219
[12.182.18.0] Ste4,12.182.19.1,10.128.19.254
the result of your script is :
Ste1 : 17 IP Addresses
Ste2 : 124 IP Addresses
Site3 : 104 IP Addresses
Site4: 253 IP Addresses
.....................
Sample --> site 1 : 30-13=17
.....
I'm looking for the number of computers in each sites that have IP address
your script gives me the number of IP address maximum possible at each site
thx

wls3

So, Ste1, S should have 2.  It should not have 17.  Is that correct?  If that is the case, all four Ste shown here should return a value of 2.  Is that also correct?  I am still unclear on what an correct result is.  For instance, if 17 is the correct result (as you Sample suggests) I am not sure why my code doesn't work.
DRRAM

WLS3
your code works but not give me what I wante
I'm looking for the number of computers in each sites that have IP address
your script gives me the number of IP address maximum possible at each site (difference between  Start IP Address   and    End IP Address)
thx
redmondb

DRRAM,

Couple of questions, please...
(1) Are you still interested in an Excel solution?
(2) If so, is there any problem with the last solution I sent?

Thanks,
Brian.

wls,
For each Site, return the number of Name-PC entries whose IP Address lies in the range specified for the Site.

DRRAM

wls3

I'm sorry. I was a little confused about the scenario.  I thought we had two separate files with data, not one source.  (Probably should have opened the sample earlier.)  Let me see if I can do something comparable with Powershell goodness.  I suspect DDRAM is closer to a solution than I am in that case provided the poster still wants to keep the data in one file.  I am sure I can do the same in Powershell.  It'll just be a little more complex than my original post.  Back to tinkering...
DRRAM

Hello redmondb
(1) Are you still interested in an Excel solution?
Yes I used and thank you very much but for question 2

(2) If so, is there any problem with the last solution I sent?
yes but please can you decrease the number of column (merge formulas) for obtaining :
- sheet Sites
Scope      Start IP Address      End IP Address      No. of PC's      Site
- sheet PC's
Name-PC              IP Address         Site

Thanks,
DRRAM

WLS3
Thx and I wait your response and please do not forget to look at (second question)
redmondb

DRRAM,

Please see attached. Each of these columns is needed - they can, of course, be hidden.

Is there anything else you need for a final solution?

Regards,
Brian.
PC-s-per-Site-V5.xlsm
DRRAM

redmondb

please can not you decrease the number of column (merge formulas) for obtaining :

- sheet Sites
Scope      Start IP Address      End IP Address      No. of PC's      Site
- sheet PC's
Name-PC              IP Address         Site

redmondb

DRRAM,

I did! "Site" is gone from both sheets.

You could do the following...
(1) On the "Sites" sheet select Column F. Copy and then Paste Specials Values (to drop the formulas).
(2) You can then delete Columns D & F on the "Sites" sheet and Column C on "PC's". (In fact you can delete the entire "PC's" sheet and columns B & C on "Sites".)

If this isn't right, please post a sample of what you want to see.

Regards,
Brian.
DRRAM

thx
PC-s-per-Site-V4-A.xlsm
redmondb

DDRAM,

The problem with your attached file is that you did not carry out the steps I gave in my previous post. I have attached another version of the file which has a macro to carry out the steps you missed. Just click on the "Drop Formulas" button and it will replace all formulas in columns D to G of "Sites" and columns C to E of "PC's". Once this has run, you can then delete or move any of the columns.

The code is...
``````Option Explicit

Sub Drop_Formulas()

Sheets("Sites").Activate
Columns("D:G").Copy
Columns("D:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Sheets("PC's").Activate
Columns("C:E").Copy
Columns("C:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Sheets("Sites").Activate

Application.CutCopyMode = False

End Sub
``````
Regards,
Brian.
PC-s-per-Site-V6.xlsm
DRRAM

redmondb

I CLICK ON THE BUTTON "DROP FORMULAS" NOTHING TO CHANGE
I THINK THE BUTTON DOES NOT WORK
redmondb

DRRAM,

Did you see a message on the ribbon asking you enable macro content? If so, you must click on the enable button to enable macros.

The button would not appear to do anything in the V6 file as I had already run it there. So all the formulas are already removed.

DDRAM, the further we go with this, the more certain I am that your original request for a script was correct. I would like to suggest that you close this question, giving all the points to wls3. Then open a new question but limit the zones to VB Script and Powershell. That way you will you'll be sure to actually get the solution you need.

I apologise that I appear to have wasted your time on a blind alley.

Regards,
Brian.
DRRAM

Redmondb

PERFECT
THANK YOU, VERY WELL WORK
drawback is it is necessary to save the file twice before deleting formulas
because as for other uses the file does not contain the formulas
is what you have a solution ..
against it by a good job and I thank you very much
Sorry if I bother you with my questions
and if I'll contact you next time after the closing of the question how I will cope

Regards,
redmondb

DRRAM,

Obviously the PC-Names file is regularly rebuilt, but what about the Sites one? Is that also recreated every time or is it a Master file that's manually updated whenever necessary?

Thanks,
Brian.
DRRAM

redmondb
THX
YES is a Master file that's manually updated whenever necessary
redmondb

DDRAM,

OK, what about a Master file - just the "Sites" sheet and a macro? The macro prompts the user for the location of the PC-Name CSV file (or it could use a specific location). The macro then loads the CSV file, adding the "formula" columns to both it and the Master file. It then removes the formulas and save the Master file and a new "PC-Names" spreadsheet.

So, you manually update the Master file when sites are added/changed. Whenever this happens (or you create a new CSV file), you run the macro on Master.

Is it clear what I'm suggesting? If so, is it what you want?

Regards,
Brian.
DRRAM

Très bien et est très interressant se que vous avez proposé
stp j'attends de tester ce que vous me ferez parvenir
DRRAM

Very well and is very interresting is that you have proposed
stp wait to test that you will send me
DRRAM

redmondb

Very well and is very interresting is that you have proposed
stp wait to test that you will send me
DRRAM

redmondb

Very well and is very interresting is that you have proposed
please I wait to test that you will send me
redmondb

Sure thing, DRRAM. I'm not sure if I'll have it this evening, but certainly by tomorrow.
DRRAM

redmondb
No problem Thank you very much
take care
redmondb

DRRAM,

Please see below for the files. Save the two files to your PC and click on the Refresh button in the spreadsheet. The code is...
``````Option Explicit

Sub Refresh_PCs()
Dim xWork      As Workbook
Dim xPCs       As Worksheet
Dim xSites     As Worksheet
Dim xLast_PC   As Long
Dim xLast_Site As Long
Dim xresponse  As Long
Dim xCSV       As String

xresponse = MsgBox("Refreshing all data by loading a new PC-Name file..." & Chr(10) & Chr(10) & " - The ""PC's"" sheet will be deleted." & Chr(10) _
& " - The ""Sites"" sheet's columns, other than A:C, will be deleted or moved." & Chr(10) & Chr(10) _
& "Do you wish to continue?", vbOKCancel, "Refresh_PCs")
If xresponse = 2 Then
MsgBox ("User selected Cancel. Nothing has been changed in the file.")
Exit Sub
End If

ThisWorkbook.Activate

' Get the CSV file's name and location...
xCSV = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", MultiSelect:=False)
If xCSV = "False" Then
MsgBox ("User did not select a file - run cancelled. Nothing has been changed in the file.")
Exit Sub
End If

Application.ScreenUpdating = False

' Delete an existing "PC's"...
On Error Resume Next
Sheets("PC's").delete
On Error GoTo 0

' Import the CSV
Set xWork = Workbooks.Open(xCSV)
xWork.ActiveSheet.Name = "PC's"
xWork.ActiveSheet.Move Before:=ThisWorkbook.Sheets("Sites")

' Some useful items...
Set xPCs = Sheets("PC's")
Set xSites = Sheets("Sites")
xLast_PC = xPCs.Range("A1").SpecialCells(xlLastCell).Row
xLast_Site = xSites.Range("A1").SpecialCells(xlLastCell).Row
xSites.Range("D:G").delete
xSites.Range("D:G").insert

' Setup PC Formulas...
Range("c2").FormulaR1C1 = "=MID(RC[-1],1,FIND(""."",RC[-1],1)-1)*2 ^ 24" & Chr(10) _
& "+MID(RC[-1],FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",1),1)+1,FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",2),1)-FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",1),1)-1)*2 ^ 16" & Chr(10) _
& "+MID(RC[-1],FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",2),1)+1,FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",3),1)-FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",2),1)-1)*2 ^ 8" & Chr(10) _
& "+MID(RC[-1],FIND(""#"",SUBSTITUTE(RC[-1],""."",""#"",3),1)+1,9999)*1"
Range("D2").FormulaR1C1 = "=IFERROR(MATCH(RC[-1],Sites!R2C4:R" & xLast_Site & "C4,1),""N/A"")"
Range("E2").FormulaR1C1 = "=IF(RC[-1]=""N/A"",""N/A"",IF(AND(RC[-2]>=INDEX(Sites!R2C4:R" & xLast_Site & "C4,RC[-1]),RC[-2]<=INDEX(Sites!R2C5:R" & xLast_Site & "C5,RC[-1])),INDEX(Sites!R2C7:R" & xLast_Site & "C7,RC[-1]),""N/A""))"
Range("C2:E2").Copy Destination:=Range("C2:E" & xLast_PC)

' Setup Site Formulas...
xSites.Activate
If xSites.AutoFilterMode = True Then xSites.AutoFilterMode = False
Range("D2").FormulaR1C1 = "=MID(RC[-2],1,FIND(""."",RC[-2],1)-1)*2 ^ 24" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",1),1)+1,FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)-FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",1),1)-1)*2 ^ 16" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)+1,FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",3),1)-FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)-1)*2 ^ 8" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",3),1)+1,9999)*1"
Range("E2").FormulaR1C1 = "=MID(RC[-2],1,FIND(""."",RC[-2],1)-1)*2 ^ 24" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",1),1)+1,FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)-FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",1),1)-1)*2 ^ 16" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)+1,FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",3),1)-FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",2),1)-1)*2 ^ 8" & Chr(10) _
& "+MID(RC[-2],FIND(""#"",SUBSTITUTE(RC[-2],""."",""#"",3),1)+1,9999)*1"
Range("F2").FormulaR1C1 = "=SUMPRODUCT(1*('PC''s'!R2C3:R" & xLast_PC & "C3>=RC[-2]),1*('PC''s'!R2C3:R" & xLast_PC & "C3<=RC[-1]))"
Range("G2").FormulaR1C1 = "=IFERROR(TRIM(MID(RC[-6],FIND(""]"",RC[-6],1)+1,9999)),RC[-6])"
Range("D2:G2").Copy Destination:=Range("D2:G" & xLast_Site)

' Setup Site Header...
Range("D1:G1").Value = Array("Start No.", "End No.", "No. of PC's", "Site")
With Range("D1:G1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
End With
Range("D1:G1").Font.Bold = True
Columns("A:G").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

' Setup PC Header...
xPCs.Activate
Range("C1:E1").Value = Array("Work", "Match", "Site")
With Range("A1:E1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
End With
Range("A1:E1").Font.Bold = True
Columns("A:E").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

'Drop Formulas and work columns...
Call Drop_Formulas
xPCs.Columns("C:D").delete Shift:=xlToLeft
xSites.Columns("D:E").delete Shift:=xlToLeft

' Finished...
Application.ScreenUpdating = True
MsgBox ("Refresh complete. Please save the file.")

End Sub

Sub Drop_Formulas()

Sheets("Sites").Activate
Columns("D:G").Copy
Columns("D:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Sheets("PC's").Activate
Columns("C:E").Copy
Columns("C:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select

Sheets("Sites").Activate

Application.CutCopyMode = False

End Sub
``````
Regards,
Brian.
PC-s-per-Site-V7.xlsm
Name-PC.csv
DRRAM

redmondb

please look at the three files attached
give me your advice and if possible and not difficult and logic
Name-PC-s.xlsx
Sites.xlsx
Result-PC-s-per-Site.xlsm
DRRAM

is not problem you can put the files:
Sites.xlsx and Name-PC-s.xlsx     in  .csv
redmondb

DDRAM,

(1) The file with the PC names...
- Is it a CSV file or an Xls?
- Is its name "Name-PC-s"?
(2) The file with the Site names...
- Is it a CSV file or an Xls?

Thanks,
Brian.
DRRAM

redmondb

(1) The file with the PC names...
- Is it  an Xls file
- the name of file is "Name-PCs"
(2) The file with the Site names...
.Xls

Thanks,
DRRAM

redmondb

you forgot me :-)
redmondb

DDRAM,

Nope! Back shortly.

Regards,
Brian.
redmondb

DDRAM,

Please see attached. There's still some work to do, but I'm away now for an hour or two and this is in case you're feeling neglected!

Regards,
Brian.

Result-PC-s-per-Site-V2.xlsm
redmondb

DDRAM,

Are we there yet?

Regards,
Brian.
Result-PC-s-per-Site-V3.xlsm
DRRAM

redmondb,

Thx :-)
You can send me both files (PCs and sites) used by the main file

Regards,
redmondb

DDRAM,

No need - a37302083.

Regards,
Brian.
DRRAM

I execute the file
I clicked on refrech after I chose the "Sites.xlsx" file  and the second "Name-PC-s.xlsx" file
I have an error
redmondb

DDRAM,

Close those file! Click on the Refresh button, click on OK for the "Do you wish to continue?" message. The Excel File Open dialogue should appear next (with "Sites.xlsx" in the title bar. Navigate to and select your Sites.xlsx. The same thing happens for Names-PC.xlsx (although the title actually says "PCs in Site.xlsx" - it's only cosmetic but we'll fix it later). The two sheets are then re-built.

If the above gives you an error, please do a screen capture and post it here.

Thanks,
Brian.
redmondb

Oops, "Close those files."
DRRAM

???
redmondb

Misprint in my preceding post.
redmondb

What I meant is that you should close "Sites.xlsx" and "Name-PC-s.xlsx" before running "Refresh".
DRRAM

YES I did
DRRAM

runtime error "5":
the error is : procedure call or argument incorrect
DRRAM

error code in attached
err.png
redmondb

Which version of Excel are you using?
DRRAM

2007
redmondb

Also, please hover your mouse over "xlast" (two lines above the highlighted line) and Excel will show you what it's value is. Please post it here.
DRRAM

With Range("A3:B" & xLast)

A3 --> Li201, Col13
B --> Li201, Col17
redmondb

Thanks, DDRAM. While the yellow line is shown, you can hover your mouse over any of the variables and Excel will show a small box with its value in it. That's what I'm looking for.

Then, stop the macro, save "Result-PC-s-per-Site_V3.xlsm" under a different name and then post it here, please.

Thanks,
Brian.
redmondb

Like this (where xLast is shown as equal to 17)...
DRRAM

same mistake
IN ATTACHED  --> xlExpression =2
ERRORv2.png
redmondb

DDRAM,

Crossing posts! It's xLast I need to know about. Please see my previous post.

(And a saved, renamed copy of the file, please!)

Thanks,
Brian.
redmondb

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
DRRAM

I use these three FILES
i have error please look at the result
Result-PC-s-per-Site-V4.xlsm
Sites.xlsx
Name-PC-s.xlsx
redmondb

DDRAM,

Great, thanks for that! The problem is that Excel thinks that the "blank" lines in Name-PC-s.xlsx have data in them. I've deleted them (see attached below) and it's running fine.

We'll worry about blank lines later, but see if this works now. <sigh>

Regards,
Brian. Name-PC-s-V2.xlsx
DRRAM

thanks
VERY WELL it works great
redmondb

DDRAM,

Excellent, thanks!

So there are two things to look at - zebra stripes and blank lines?

Regards,
Brian.
redmondb

Hi, DDRAM.

Both of the attached files look for and delete any rows in the Sites and PC's files with blank cells in Column A.

I've restored the conditional formatting code in Result-PC-s-per-Site_V5.xlsm as it doesn't seem to cause any problems for me in 2007.

Please try Result-PC-s-per-Site_V5.xlsm against your files with the blank rows. On the other hand, if that gives an error with the conditional formatting code then please try No_Cond.xlsm.

Regards,
Brian.
Result-PC-s-per-Site-V5.xlsm
No-Cond.xlsm
DRRAM

redmondb
was always the error file "Result-PC-s-per-Site_V5.xlsm"
I tried "No_Cond.xlsm" it is excellent thank you.
I will use "No_Cond.xlsm" it's very good

I have one request please:

I need a second version summarize
Indeed, it attaches a file contains two sheets: PCs and Sites
I'll just fill Site column in the PCs sheet for to know what PC in what site.
I put the "Refresh" button in the sheet PCs
Please you can make the change of the script.

Regards,

PCs-in-Sites.xlsx
redmondb

DDRAM,

Sorry, I'm not clear what you want. Please post the sources and the output.

Thanks,
Brian.
DRRAM

redmondb
I need a second summarize version
on attacher le fichier "PCs-in-Sites.xlsm" contains two sheets: PCs and Sites
- Sites sheet include the name of the site over the beginning and end of the ip address.
question : know what PC in what site? example PC1---> Site ?
or in which site I find pc1 or 2 .....
resultat : fill the "site " column in the PCs sheet (after click on the Refresh button In the PCs sheet )

Please you can make the change of the script button.

Regards,
PCs-in-Sites.xlsm
redmondb

DDRAM,

(1) More than a week ago, I mentioned that the Sites sheet had to be sorted. I have now changed the main files to do this...
Result-PC-s-per-Site-V6.xlsm
No-Cond-V2.xlsm
(2) Regarding your new request - we're already a long way from your original question. I'm afraid I simply don't have the the time to deliver on a whole new set of requirements! However, I have added the formulas to your latest file to provide the data you want. (Please note that I had to sort the Sites sheet.)
PCs-in-Sites-V2.xlsm
Regards,
Brian.
DRRAM

redmondb
I will use this file "No-Cond-V2.xlsm" is very well done
but
Please in the file "Pcs-in-sites-v2.xlsm" Can you explain the three first line (line 2 to line 5) they have "Match" = 1 and it is clear that PCs does not belong to a site
normally it's going to be  "Match" = N/A
????
also in  (line 2462 to line 2723  -->  "Match" = 6) and (line 2814 -->  "Match" = 17
thank you very much and I look wait to your response
redmondb

DDRAM,

There are 3 ways that MATCH can work and this is set by it's third parameter...
1  = Find the largest value that is less than or equal to the lookup value. (The column to be looked up must be sorted ascending.)
0  = Find the first value that is exactly equal to the lookup value. (The column to be looked up may or may not be sorted)
-1 = Find the smallest value that is greater than or equal to the lookup value. (The column to be looked up must be sorted descending.)

I am using 1, so that is why the MATCH finds the first site (the second site's "Start - No." is greater than the lookup value).

So the Match can find a site which is not relevant to the PC - as happens here. But then the formula in column E checks to see if the site is relevant - which it is not and so "N/A" is displayed.

Column D is purely a work column - the important column is E. (You could say that a match in Column D means "there is a site which might contain this PC.")

I'm afraid that's all from me tonight. I'll respond to any further queries tomorrow.

Regards,
Brian.
DRRAM

redmondb

in this file "No-Cond-V2.xlsm" after refresh I have the "Nombre of PCs in Site" sheet  empty

test in attachement

Name-PC-s.xlsx
No-Cond-V2.xlsm
Sites.xlsx
DRRAM

redmondb
ok thanks take care of yourself
redmondb

DDRAM,

No, the data was all there - the FreezePane was just in the wrong place.

Regards,
Brian.
No-Cond-V3.xlsm
DRRAM

redmondb
Thx for your help
Please in the file "PC-in-Sites-V2.xlsm" (attached)
I added a site (line 132 in sheet "site")
and
I added the sheet PCs a computer (line 3472)
error: the column "site" = N / A (in PCs sheet) for the new line 3472
Normally,i should have found the name of site I created "new test"
Thx
PCs-in-Sites-V2.xlsm
DRRAM

Sorry I bother you
please I wait your correction on the error described message  in the previous
Regards,
DRRAM

redmondb
redmondb

Hi, DDRAM.

Sorry I'm away from PC at the moment and have no access to Excel. Could you save the PC and Sites as CSV's and post them here, please?

Thanks,
Brian.
DRRAM

redmondb
error in the first method
Thx for your help
Please in the file "PC-in-Sites-V2.xlsm" (attached)
I added a site (line 132 in sheet "site")
and
I added the sheet PCs a computer (line 3472)
error: the column "site" = N / A (in PCs sheet) for the new line 3472
Normally,i should have found the name of site I created "new test"
Thx
PCs-in-Sites-V2.xlsm
Sites.csv
PCs.csv
redmondb

Thanks, DDRAM. I don't see why it didn't match. Can you post the CSV versions of the input files, please?

Thanks,
Brian.
DRRAM

in the first method
was a single file "PC-in-Sites-V2.xlsm" contain two sheets (Sites and PCs)
The last line in the sites sheet contains :
[10.128.1.0] new test;10.128.1.50;10.128.1.200;176161074;176161224;new test
and
last row in the PCssheet contains :
XFL234545;10.128.1.199;176161223;N/A;N/A;;;;
error: the column "site" = N / A (in PCs sheet) for the new line add
Normally,i should have found the name of site I created "new test"
Thx
DRRAM

NO
WAIT
DRRAM

redmondb
ok
in the first method
was a single file "PC-in-Sites-V2.xlsm" contain two sheets (Sites and PCs)
The last line in the sites sheet contains :
[12.182.1.0] new test;12.182.1.50;12.182.1.200;213254450;213254600;new test
and
last row in the PCssheet contains :
XFL234545;12.182.1.199;213254599;1;N/A
error: the column "site" = N / A (in PCs sheet) for the new line add
Normally,i should have found the name of site I created "new test"
Thx
DRRAM

213254450 < 213254599 < 213254600
redmondb

DDRAM.

OK got it. When you added the new Sites entry you didn't sort the sheet. As I mentioned above, it has to be sorted on "Start - No.".

Regards,
Brian.
DRRAM

I did not understand
What can I do to update the data.were obtained:
XFL234545;12.182.1.199;213254599;1;N/A
as we : 213254450 < 213254599 < 213254600
normally it will give us
last row in the PCssheet contains:
XFL234545;12.182.1.199;213254599;133;new test
error how I can resolve
thx
redmondb

DRRAM,

The Sites sheet must be sorted by "Start - No.". As soon as you sort it, the formulas for XFL234545 will immediately find its site details.

The reason the sort is necessary, is as mentioned in 37324221 ...
There are 3 ways that MATCH can work and this is set by it's third parameter...
1  = Find the largest value that is less than or equal to the lookup value. (The column to be looked up must be sorted ascending.)

Regards,
Brian.
DRRAM

redmondb,

Very well, it works
Thank you very much and I wish you a nice day
redmondb

Glad you're sorted, DDRAM!
DRRAM

redmondb,
I thank you very much,
just a question
How can I do if I am going to ask for changes after acceptances solutions
redmondb

DRRAM,

I keep an eye on "my" questions for a couple of weeks after they're closed.

Regards,
Brian.
DRRAM

I've requested that this question be closed as follows:

Accepted answer: 0 points for DRRAM's comment http:/Q_27488601.html#37328386

for the following reason:

thank you very much &quot;redmondb&quot; your work is constructive
DRRAM

I made a mistake I closed the question without putting points
redmondb

DRRAM,

!

Assuming that you didn't want to do this, please click on "Request Attention" (it's at the bottom of your original question).

Regards,
Brian.
DRRAM

thank you very much "redmondb" your work is constructive
DRRAM

I've requested that this question be closed as follows:

Accepted answer: 0 points for DRRAM's comment http:/Q_27488601.html#37328386

for the following reason:

thank you very much &quot;redmondb&quot; your work is constructive
DRRAM

....
DRRAM

I've requested that this question be closed as follows:

Accepted answer: 0 points for DRRAM's comment http:/Q_27488601.html#37328386

for the following reason:

thank you very much &quot;redmondb&quot; your work is constructive
redmondb

DRRAM,

Do you really want to close this question with 0 points to your post?

Regards,
Brian.
redmondb

Thanks, _alias99. I wasn't sure whether the author should object to themselves!
DRRAM

no but I have an error I can not put notes
I can not put notes
DRRAM

I've requested that this question be closed as follows:

Accepted answer: 0 points for DRRAM's comment http:/Q_27488601.html#37328386

for the following reason:

thank you very much &quot;redmondb&quot; your work is constructive
redmondb

DRRAM,

Sorry, I don't understand your post.

If you're having a problem with closing this question, then don't worry about it. We've both spent far too much time on this - just abandon this question and it'll automatically be closed in four days for 0 points.

All the best,
Brian.
DRRAM

for redmondb
Grading Tips  -->  A

Was the solution complete?  --> Yes
Was the solution accurate?  --> Yes
Was the solution easy to follow?  --> Yes
DRRAM