getting column and row names if a cell has a value

[Allyn Okun]

Hello,
I am trying to pull data out of a large Excel spreadsheet to get a
slimmed down version..
I have a large spreadsheet (100+ columns by 500+ rows, and 12
worksheets) that I'm trying to make sense of in order to import into
another program.

Each Column has a four-digit code for a "name", each row has a five-
digit code for its "name." The majority of cells have a value of '0'.
I am trying to get excel to look at the range, and if there is a value
that is not zero in a cell, give me the column name in one column, the
row name in the next column then the values of that cell in all 12
worksheets (months) in the next 12 columns.

I keep coming closer and closer, but have not reached the grail yet.
Once columns A and B (the four and five digit codes) are populated, I
have been able to get the program to look for values in all months in
that cell using Hlookup and Vlookup. At one point, I was able to get
the row name, if I knew the column and vice-versa, but I haven't been
able to get the whole thing.

EX.
4000 4010 4011 4012 4020 4040
00000 0 0 0 0 0 0
00010 0 0 0 0 3750 0
00020 0 0 0 0 0 0
00025 0 0 0 0 0 0
00030 0 0 0 0 0 0
00035 0 0 0 0 0 0


the formula would search through the above, find that there is a non-
zero value, and tell me that the column name is '4020' in one column,
the row name is '00010' in the next column, then the value (3750) in
column 3.

Each set of data has multiple cells with non-zero data, and I need
excel to search all of it to return all of the combinations (they are
account numbers), then search the next worksheet for values (there may
be a number in The April worksheet that wasn't used in the first three
months.

I hope this makes something resembling sense.
I appreciate any and all help.

Thank you,
SmokinZBT

 

[Roger Govier]

Hi

If you can use a VBA solution, the following will probably achieve what you
want.
Insert a new sheet in your file and name if "myList"

Copy the code as instructed at the end of this message.
Alt+F8>select macro FindValues>Run

Sub FindValues()
Dim i As Long, j As Long, lr As Long, lc As Long, counter As Long
Dim wsd As Worksheet, ws As Worksheet
Dim c As Range
Application.ScreenUpdating = False
Set wsd = ThisWorkbook.Sheets("myList")
wsd.Cells.ClearContents
wsd.Cells(1, 1) = "Sheet": wsd.Cells(1, 2) = "Column"
wsd.Cells(1, 3) = "Row": wsd.Cells(1, 4) = "Value"
j = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "myList" Then
ws.Activate
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To lc
counter = WorksheetFunction.Sum _
(ws.Range(Cells(2, i), Cells(lr, i)))

If counter > 0 Then
Cells(1, i).Select
Selection.AutoFilter Field:=i, Criteria1:=">" & 0
For Each c In Range(Cells(2, i), Cells(lr, i)) _
.SpecialCells(xlCellTypeVisible)
If c.Value > 0 And c.Row > 1 Then
wsd.Cells(j, 1) = ws.Name
wsd.Cells(j, 2) = ws.Cells(1, i).Value
wsd.Cells(j, 3) = ws.Cells(c.Row, 1).Value
wsd.Cells(j, 4) = ws.Cells(c.Row, i).Value
j = j + 1
End If
Next
ws.ShowAllData
End If
Next i
End If
Next ws
wsd.Activate
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[Allyn Okun]

Thank you. This gets me pretty close. I can manually make the
adjustments that I need from here.

 

[Roger Govier]

Hi Allyn

What adjustments are they?
Perhaps we can modify the code to include them.
Were they part of your original spec that I missed?

 

[Allyn Okun]

Roger,
Just getting the output to look something like:

4321 98765 0 0 0 0 453 0 0 27 0 0 0 0

Where 4321 is the 4-digit, 98765 is the 5-digit and the next 12
columns show the amount in months 1-12 (Jan - Dec, the worksheets).

Allyn