• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re-arranging cell values in an excel file

karthick87

karthick87

Member
Hello All,

I have an excel file with an output copied to it. The file has an IP value (i.e A1) followed by the package name (i.e A2) in the next cell. For some IP address, there will be no Package name and therefore we need to leave the package name as empty (i.e B2). So I want the output to be like the following

A1. IP_Address1 B1. Package_Name
A2. IP_Address2 B2.
A3. IP_Address3 B3. Package_Name

I have attached a sample file with this message, can someone please help me out?
 

Attachments

  • Orig_File.xlsx
    9.4 KB · Views: 5
Thanks to both of you @bosco_yip & @rahulshewale1. Both works like a charm.

After applying the formula in C2, I get the results I wanted to. So now I have IP Address in ColumnA and Package_Name in ColumnC. However, I also wanted to remove the rows (A) that contain's the package name keeping the IP address as it is. I don't want to select each every row manually and delete it. Is there an easier way to do the same?

Herewith attaching the sample file.
 

Attachments

  • Orig_File_Modified.xlsx
    9.5 KB · Views: 5
Hello, @rahulshewale1 @bosco_yip I have applied the given formula's in my excel sheet which has the similar data. But it seems, it doesn't work. Do I need to correct anything else in the given formula?

@bosco_yip

Do I need to modify anything in the formula to work in a different excel sheet with the similar set of data?

=IFERROR(LOOKUP(1,0/('Actual File'!A$1:A$45=A2)/(LEFT('Actual File'!A$2:A$46)="p"),'Actual File'!A$2:A$46),"")

Because in your formula, i have seen that you have mentioned 'Actual File', I suppose that is the Sheet Name. Please correct me if I am wrong.

@rahulshewale1

Similarly, in your formula below, you have also mentioned 'Actual File'. Is that the Sheet Name?

=IF(ISLOGICAL(SEARCH(192,INDEX('Actual File'!$A$1:$A$46,MATCH(A2,'Actual File'!$A$1:$A$46,0)+1))>0),"",INDEX('Actual File'!$A$1:$A$46,MATCH(A2,'Actual File'!$A$1:$A$46,0)+1))

Because the new excel file's sheet name is different. So just wanted to know, how to work out the above formula's in a different excel file which has the same pattern?
 
You must log in or register to reply here.
Back
Top