Query - Data Layout

[mralmackay]

I have a series of data as set out below which I need to amend in to a
flat file. The requirement is for each User ID record to have a line
for every profile that the user has.

Currently my excel spreadsheet has every user ID going across in
columns per user (see below). I need this to appear as per example 2.

Column A Column B Column C Column D Column E
User1 Profile1 Profile2 Profile3
User2 Profile2 Profile4
User3 Profile4 Profile5 Profile6 Profile7


Should appear like (Example 2):
Column A Column B
User1 Profile1
User1 Profile2
User1 Profile3
User2 Profile2
User2 Profile4
User3 Profile4
User3 Profile5
User3 Profile6
User3 Profile7

Please can you assist with this - I'm not sure how this is done,
however assuming this could be something that could be done in VBA?

Many Thanks, Al.

 

[Arvi Laanemets]

Hi

One possible way:
On new sheet, create a table
User Profile

A2:B2 in this table enter formulas (I assume original data are on Sheet1,
and that there can be up to 7 different profiles per user)
=IF(OFFSET(Sheet1!$A$1,INT((ROW()-2)/7),)="","",OFFSET(Sheet1!$A$1,INT((ROW()-2)/7),))
=IF(OFFSET(Sheet1!$A$1,INT((ROW()-2)/7),MOD(ROW()-2,7)+1)="","",OFFSET(Sheet1!$A$1,INT((ROW()-2)/7),MOD(ROW()-2,7)+1))
(When the max. number of profiles per user is different from 7, replace the
number 7 in formula with your own value)
Copy formulas down for so many rows as you need - a table is created.

Now you have to discard rows without profile value. There are several ways
for it - a couple of them below:
1. Using Paste Special, convert formulas to values. Using Autofilter, copy
all rows where Profile is not empty, and paste copied cells into new sheet.
2. Define the created table as a named range. Use this range as a source for
an ODBC query, to retrieve all rows where Profile is not empty, into a new
table on another sheet.

 

[mralmackay]

Hi Arvi,

Thanks for your help so far with this - however, I'm still having
problems which I hope you may be able to help with?

I have a VALUE# error message. To confirm a few additional details
which may assist, apologies for not previously stating these but I was
trying to keep the text down to a minimum initially!

Sheet name where the data is stored: Dialog Users
Data range to read: Column H, Row 5 through to Column S: Row 1000 (12
columns in total).

Hope this helps? Any further info', please let me know.

Thanks again for your time on this, much appreciated. Al.

 

[Arvi Laanemets]

Hi

A2=IF(OFFSET('Dialog Users'!$H$5,INT((ROW()-2)/7),)="","",OFFSET('Dialog
Users'!$H$5,INT((ROW()-2)/7),))
B2=IF(OFFSET('Dialog
Users'!$H$5,INT((ROW()-2)/7),MOD(ROW()-2,7)+1)="","",OFFSET('Dialog
Users'!$H$5,INT((ROW()-2)/7),MOD(ROW()-2,7)+1))


Arvi Laanemets

 

[mralmackay]

Hi Arvi,

Just a quick note to say thanks very much for your help on this -
worked a treat. Would it be possible at all to explain the logic of
the formula as this will be helpful to me for any similar future
requirements.

Thanks again, Al.

 

[Arvi Laanemets]

Hi

Let's analyze the formula in column A. In general, it is a formula
=IF(Reference="","",Reference)
, and it is needed because the reference to an empty cell returns 0
otherwise.

Further
=OFFSET(BaseCell,ShiftRows,ShiftColumns)
returns a reference to some cell relatively to BaseCell. In my formula, it
is cell 'Dialog Users'!H5 for 7 rows (cells A2:A8, the offset formula is
OFFSET(BaseCell,0,0)), cell 'Dialog Users'!H6 for next 7 rows (A9:A15, the
offset formula is OFFSET(BaseCell,1,0)), etc. As the reference returned by
formula in column A is always to same column on source sheet, I selected the
base cell from same column -so I could omit the column shift in this formula
at all.

INT((ROW()-2)/7)
There is nothing to explain at all - it's too straightforward. Insert this
as formula into some cell, copy down, and look what you get for different
rows.


Arvi Laanemets