Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Condidional output  a few cells  from  adjacent сolumns

Posted on 2014-07-15
15
Medium Priority
?
139 Views
Last Modified: 2014-11-15
Hi

There is 2 columns in Excel worksheet - "Invoices" and "Value". I need to output only the cells in column "Invoices"  which corresponding  "Values" ​​are blank cells. I am attaching  the explanatory image. The result should look like this :
                               1815
                               2014
                              18013
                              22018
                              16014
                              15255

Any help appreciated
Ivoices.gif
0
Comment
Question by:nesher13
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 19

Expert Comment

by:Montoya
ID: 40196739
try the ISBLANK function

=IF(ISBLANK(B4),A4) ... substitute B4 for your value column and A4 for your invoice column. Copy to all cels below.

Let me know if you have any other questions
0
 
LVL 23

Expert Comment

by:NBVC
ID: 40196808
If you are trying to output a separate list elsewhere in the sheet, then try:

=IFERROR(INDEX($G$3:$G$12,SMALL(IF($H$3:$H$12="",ROW($H$3:$H$3)-ROW($H$3)+1),ROWS($H$3:$H3))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copy down.
0
 

Author Comment

by:nesher13
ID: 40196872
Iammontoya

I have no problem to write operator "if" "else".
My problem is that I'm confused with the loops "For"  or "Foreach"
I also can not refer to the adjacent  column or the adjacent cell  with structure offset.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
LVL 10

Expert Comment

by:Prashant Girennavar
ID: 40196895
if you like to use powershell below is the code.

Please convert your excel to csv  and run it

$Line = @()
import-csv C:\input.csv | %{
if($_.Value -eq "$null")
{
$line+= $_.name
}
}

$line | out-file C:\Formattedoutput.csv

Open in new window


let me know if you need more help

Thanks,

-Prashant Girennavar.
0
 

Author Comment

by:nesher13
ID: 40196905
NBVC

If you are trying to output a separate list elsewhere in the sheet
I am not. I now no matter where in Excel to store selected invoices numbers.  Likely they will be piped out
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40196914
Hey there,

Sorry about that. I did not see anything about you trying to use For/Next loops. Perhaps there's a requirement here I did not get from the post?

Perhaps if you can give me more specific information?

Thanks!
0
 

Author Comment

by:nesher13
ID: 40196990
Iammontoya

This my code:
$ExcelPath = "c:\users\user1\documents\Template.xlsx"


$Excel = New-Object -ComObject "Excel.Application"
$Workbooks = $Excel.Workbooks
$Workbook = $Workbooks.Open($ExcelPath)
$Worksheets = $Workbook.Worksheets
$Sheet1 = $Worksheets.Item(1) | Where {$_.name -eq "sheet1".select}
$Range = ("H3:H12")
$Rows = $Range.Rows



For ($k = 3; $k -le $Range; $k = $k + 1){

If ($Value -ne "") and ($Value -ne $null) {"no-blank value in row $k"}
   
      Else {"blank value in row"}
}}

$result | Where {!$_.Values} | Foreach {$_.Invoices}

$Excel.Quit()
0
 

Author Comment

by:nesher13
ID: 40197054
PrashantGirennavar

Please convert your excel to csv  and run it
I am save the excel.xlsx file as .csv (coma delimeted)
when script is running I am receive message:
WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers.

What am I doing wrong?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 40197268
I am not sure what your comment to me means.  Can you elaborate?

My formula will "copy" over the invoices that have no values to whereever you put the formula in.
0
 
LVL 10

Expert Comment

by:Prashant Girennavar
ID: 40198425
Hello,

 I think , you are not saving Csv file properly with the headers.

Follow below steps , Open a excel and put the value like below

screen1save the excel as csv like below.

screen2
post that , run the script.

Make sure to change the path in the script accordingly

Let me know if this does not work.

Thanks,

-Prashant Girennavar.
0
 

Author Comment

by:nesher13
ID: 40201470
Hi folks,

unfortunately none of the answers does not lead to the desired result
0
 
LVL 10

Expert Comment

by:Prashant Girennavar
ID: 40201671
Did you try the script which is provided by me?

If yes what is the output? Are you getting any error message while executing it.

When I run the script here. it works fine.

Let me know where you are stuck.

Thanks,

-Prashant Girennavar.
0
 

Accepted Solution

by:
nesher13 earned 0 total points
ID: 40208444
Solution is as follows:


Open in new window

$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\users\dov143\Documents\Template.xlsx")
$ws = $wb.Worksheets.Item(1)

$result = @()

[int]$rmax = $ws.Range("G65536").End(-4162).Row
$ws.Range("G3:G$rmax") | Where {$_.Value2} | Foreach {
      $col = $_.Column+1
      $row = $_.Row
      $val = $ws.Cells.Item($row,$col)
      
      $result += [pscustomobject]@{
            Invoices = $_.Value2
            Values = $val.Value2
      }
}
      
$result | Where {!$_.Values} | Foreach {$_.Invoices}

$ex.Quit()

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40444264
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question