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. |
Query - Data Layout
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<attarkon.ee ) wrote in message oups.com... 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. |
Query - Data Layout
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<attarkon.ee ) wrote in message oups.com... 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. |
Query - Data Layout
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 wrote in message ups.com... 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<attarkon.ee ) wrote in message oups.com... 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. |
Query - Data Layout
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 wrote in message ups.com... 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<attarkon.ee ) wrote in message oups.com... 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. |
Query - Data Layout
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 wrote in message oups.com... 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 wrote in message ups.com... 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<attarkon.ee ) wrote in message oups.com... 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. |
All times are GMT +1. The time now is 09:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com