Sort PCs in Sites

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

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

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

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.

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

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

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

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

Select allOpen in new window

Not quite a one liner, but, close.

redmondb thx verry mutch I am trying it

wls3 please how I can test this code

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.

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

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

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

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

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/

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

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.

I will be running frequently every week

Thanks,

redmondb

I will be running frequently every week

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

Thanks,

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.

DDRAM,

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

Regards,
Brian.

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.

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

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

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.

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

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

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??

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

Select allOpen in new window

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.

@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.

@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.

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

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.

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

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.

WLS3
you have an answer on this question please
https://www.experts-exchange.com/questions/27494333/DHCP-script.html?cid=239&anchorAnswerId=37288084#a37288084

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...

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,

WLS3
Thx and I wait your response and please do not forget to look at (second question)
https://www.experts-exchange.com/questions/27494333/DHCP-script.html?cid=239&anchorAnswerId=37288084#a37288084

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

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

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.

please in attachement
thx
PC-s-per-Site-V4-A.xlsm

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

Select allOpen in new window

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

redmondb

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

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.

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,

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.

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

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.

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

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

redmondb

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

redmondb

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

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

redmondb
No problem Thank you very much
take care

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
Application.DisplayAlerts = False
    Sheets("PC's").delete
Application.DisplayAlerts = True
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
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
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
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
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

Select allOpen in new window

Regards,
Brian.
PC-s-per-Site-V7.xlsm
Name-PC.csv

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

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

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.

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,

redmondb

you forgot me :-)

DDRAM,

Nope! Back shortly.

Regards,
Brian.

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

DDRAM,

Are we there yet?

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

redmondb,

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

Regards,

DDRAM,

No need - a37302083.

Regards,
Brian.

please redmondb,

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

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.

Oops, "Close those files."

???

Misprint in my preceding post.

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

YES I did

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

error code in attached
err.png

Which version of Excel are you using?

2007

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.

With Range("A3:B" & xLast)

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

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.

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

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

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.

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

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

thanks
VERY WELL it works great

DDRAM,

Excellent, thanks!

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

Regards,
Brian.

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

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

DDRAM,

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

Thanks,
Brian.

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

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.

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

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.

redmondb
Other error please

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

redmondb
ok thanks take care of yourself

DDRAM,

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

Regards,
Brian.
No-Cond-V3.xlsm

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

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

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.

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

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

Thanks,
Brian.

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

NO
WAIT

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

213254450 < 213254599 < 213254600

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.

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

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.

redmondb,

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

Glad you're sorted, DDRAM!

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

DRRAM,

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

Regards,
Brian.

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

I made a mistake I closed the question without putting points

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.

thank you very much "redmondb" your work is constructive

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

....

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,

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

Regards,
Brian.

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

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

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,

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.

for redmondb
Select a Grade:
Grading Tips  -->  A

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

thx

redmondb
Sorry, finally the problem is solved I chose another solution

thx All the best,

Thanks, DRRAM.