retrieving multiple corresponding values with variable rows/column

[soph]

Hi

I have one sheet with a data export which has names in one column and in the
rows has multiple pieces of information in different columns. The problem
is that the information in the rows for one name is not necessarily in the
same columns as the information in rows for another, despite being the same
information. eg:


Bob Smith address (blank) age DOB job (blank)
(blank)
Sam Jones (blank) address (blank) age DOB (blank)
job

Additionally, some rows are blank also. What I want to do is have this
information copied over to a new sheet with the blanks removed so that I get
the below (consecutive columns):

Bob Smith address age DOB job
Sam Jones address age DOB job

So far I have managed to return all names without blanks to a new sheet
using the formula:
=IF(ROWS(C$2:C2)<=C$1,INDEX('ICC establishment
export.csv'!$C$5:$C$1500,SMALL(IF('ICC establishment
export.csv'!$C$5:$C$1500<>"",ROW('ICC establishment
export.csv'!$C$5:$C$1500)),ROWS(C$2:C2))-ROW('ICC establishment
export.csv'!C$5)+1),"")

Now I just need to return the corresponding row data without the blanks. I
thought it would be a matter of identifying the columns with data in them
from a total range and then returning the data within those columns
referenced but I am struggling to put this in a formula. If anyone could
help it would be greatly appreciated!

Hope this makes sense )
Cheers
Soph

 

[Joerg Mochikun]

You could make a copy of your sheet and remove the blanks:

To remove blanks in columns:
1) Select your data area
2) push F5 => Special => Blanks => OK
3) Right-click => Delete => Shift cells left => OK

To remove blank rows:
1) Select your name column
2) push F5 => Special => Blanks => OK
3) Right-click => Delete => Entire row => OK

Cheers,
Joerg

 

[pub]

Hi

I have one sheet with a data export which has names in one column and
in the
rows has multiple pieces of information in different columns. The
problem
is that the information in the rows for one name is not necessarily in
the same columns as the information in rows for another, despite being
the same information. eg:


Bob Smith address (blank) age DOB job
(blank)
(blank)
Sam Jones (blank) address (blank) age DOB
(blank) job

Additionally, some rows are blank also. What I want to do is have
this information copied over to a new sheet with the blanks removed so
that I get the below (consecutive columns):

Bob Smith address age DOB job
Sam Jones address age DOB job

So far I have managed to return all names without blanks to a new
sheet using the formula:
=IF(ROWS(C$2:C2)<=C$1,INDEX('ICC establishment
export.csv'!$C$5:$C$1500,SMALL(IF('ICC establishment
export.csv'!$C$5:$C$1500<>"",ROW('ICC establishment
export.csv'!$C$5:$C$1500)),ROWS(C$2:C2))-ROW('ICC establishment
export.csv'!C$5)+1),"")

Now I just need to return the corresponding row data without the
blanks. I thought it would be a matter of identifying the columns
with data in them from a total range and then returning the data
within those columns referenced but I am struggling to put this in a
formula. If anyone could help it would be greatly appreciated!

Hope this makes sense )
Cheers
Soph

this may or may not work for you.
since its a .csv file.
- open the .csv in notepad
- find ,,
- replace with ,
then open it back up in excel and the blanks should be gone.
ust a suggestion , but i hope this helps.

 

[soph]

These are great thanks so much! However I realise that I didn't actually the
critical piece / I would like to create a formula (or macro if formula
wont work) to do this for me in the new workbook/sheet when I open this up.
The data will change month to month and so would like to automate this task
as much as possible...so sorry!!!!

 

[Joerg Mochikun]

I see no reason to use formulas (assuming that the there is no need to keep
dynamic references to the original data). Here is my earlier proposal as
macro:

Sub DeleteBlanks()
With ActiveSheet.UsedRange
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Open your new worksheet (with the already copied data) and run the macro.
The macro assumes that all rows with a blank cell in the first column can
be deleted, so use with care and test thoroughly.

Joerg