Query - Data Layout

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.

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



<[email protected]> wrote in message
news:[email protected]...
>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.
>

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 wrote:

> 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.
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >

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



<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>
> > 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(Sheet
1!$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.
> >
> >
> > --
> > Arvi Laanemets
> > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> >
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > >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.
> > >
>

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 wrote:

> 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
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >
> > > 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(Sheet
> 1!$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.
> > >
> > >
> > > --
> > > Arvi Laanemets
> > > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> > >
> > >
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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.
> > > >
> >

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


<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>
> > 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
> >
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > >
> > > > 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(Sheet
> > 1!$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.
> > > >
> > > >
> > > > --
> > > > Arvi Laanemets
> > > > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> > > >
> > > >
> > > >
> > > > <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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.
> > > > >
> > >
>