Please help to me to get the data by using Lookup formulas or Macro

[vinu]

Hello Team,


I enclosed the link(for file) below, I explained the problem in the "Raw Data" sheet.I need the solution like "Result sheet". Could you please give me solution for this by using any lookup formula or Macro.


Link for the file - example.xls


http://www.esnips.com/doc/71a3f7ef-faf4-4462-acb9-f498b286ae26/example


Regards,

Vinod

 

[Hui]

Vinu

Please don't use sites that require people to install downloaders, most people read (Virus, Malware etc) when we see that!

Secondly, esnips installs itself as the default home page and re-starts your browser, stopping any existing downloads, which the esnips downloader tries to take over, and it sets itself as the default search with Firefox Search.

 

[Hui]

Vinu


This will get most of it done

Goto the Raw data Page and Run the macro below

The results are on the Raw Data page over at Column AA:AG

You would be best to now sort and re-arrange the data to get the final table you are after

=========

[pre]

Sub ROLLUP()

Dim c As Range
Dim Col As Integer
Col = 1

For Each c In Range("a2:a879")

'Level 1
If c.Value = "" Then
Cells(c.Row, 33).Value = Cells(c.Row - 1, 33).Value
Else
Cells(c.Row, 33).Value = c.Value
GoTo Skip
End If

'Level 2
If Cells(c.Row, 2) = "" Then
Cells(c.Row, 32).Value = Cells(c.Row - 1, 32).Value
If Cells(c.Row, 3) >= 10000 And Cells(c.Row, 3) <= 99999 Then
Col = 3
GoTo Centre_No
End If
Else
Cells(c.Row, 32).Value = Cells(c.Row, 2).Value
GoTo Skip
End If

'Level 3
If Cells(c.Row, 3) = "" Then
Cells(c.Row, 31).Value = Cells(c.Row - 1, 31).Value
If Cells(c.Row, 4) >= 10000 And Cells(c.Row, 4) <= 99999 Then
Col = 4
GoTo Centre_No
End If
Else
Cells(c.Row, 31).Value = Cells(c.Row, 3).Value
GoTo Skip
End If

'Level 4
If Cells(c.Row, 4) = "" Then
Cells(c.Row, 30).Value = Cells(c.Row - 1, 30).Value
If Cells(c.Row, 5) >= 10000 And Cells(c.Row, 5) <= 99999 Then
Col = 5
GoTo Centre_No
End If
ElseIf Cells(c.Row, 4) >= 10000 And Cells(c.Row, 4) <= 99999 Then
Col = 4
GoTo Centre_No
Else
Cells(c.Row, 30).Value = Cells(c.Row, 4).Value
GoTo Skip
End If

'Level 5
If Cells(c.Row, 5) = "" Then
Cells(c.Row, 29).Value = Cells(c.Row - 1, 29).Value
If Cells(c.Row, 6) >= 10000 And Cells(c.Row, 6) <= 99999 Then
Col = 6
GoTo Centre_No
End If
ElseIf Cells(c.Row, 5) >= 10000 And Cells(c.Row, 5) <= 99999 Then
Col = 5
GoTo Centre_No
Else
Cells(c.Row, 29).Value = Cells(c.Row, 5).Value
GoTo Skip
End If

'Level 6
If Cells(c.Row, 6) = "" Then
Cells(c.Row, 28).Value = Cells(c.Row - 1, 28).Value
Col = 7
GoTo Centre_No

ElseIf Cells(c.Row, 6) >= 10000 And Cells(c.Row, 6) <= 99999 Then
Col = 6
GoTo Centre_No
Else
Cells(c.Row, 28).Value = Cells(c.Row, 6).Value
Col = 7
GoTo Centre_No
End If

GoTo Skip:

Centre_No:
If Val(Cells(c.Row, Col)) >= 10000 And Val(Cells(c.Row, Col)) <= 99999 Then Cells(c.Row, 27).Value = Cells(c.Row, Col).Value

Skip:

Next

End Sub

[/pre]

 

[vinu]

Hi Hui,


Thanks a lot for your code. Its working superb!!


How do I make edit to the code if I need these changes:


1. If I want to get rollup codes description instead of the code No.

2. If I want to get up both code and description( by concatenating) how do I get this.


Regards,

Vinu.

 

[Hui]

For Pt 2.

Change these lines

[pre]

Centre_No:
If Val(Cells(c.Row, Col)) >= 10000 And Val(Cells(c.Row, Col)) <= 99999 Then
Cells(c.Row, 26).Value = Cells(c.Row, Col).Value
Cells(c.Row, 27).Value = Cells(c.Row, Col+1).Value
End If

skip:

[/pre]

and then join Column Z and AA manually

 

[vinu]

Hi Hui,


I made changes for the last few lines codes, but its giving only description only for center code .. For other Rollup like(6th, 5th.. level) its giving only code not the description. I need like below for eg: 70426 center no.


70426 Bench Plaza Building N2400 Office Services N3010 Total Real Estate and Store Development EVP05 LEGAL & RESD MAU00 OFFICE UNITS BY EVP A0002 TOTAL OFFICE BUDGET UNITS


Thanks,

Vinu.

 

[Hui]

Vinu


Using the original code you can add a few columns to the right of AE and do some Index(Match()) to pull out the extra Descriptions for each field if you want

 

[vinu]

Hui,


Thanks, will do it.