Appenda data from one sheet to another

[MASOODAHMAD]

Hi Everyone. Hope you're all well.

I'm looking for something. Can you please suggest some trick, formula or a macro to achieve this:

Source:
1. I have data (Job Details) in 'OperatorDetails' sheet, where Operators fill in the details on daily basis. For example:

Job Number	Brand	Status	PrinterName	Date Uploaded
02786a_VEN	Thermal	Files Transferred	Printer01	4/30/18
02787a_VEN	EQP	Rejected
02788a_VEN	EFS
02789a_VEN	EFS	Files Transferred	Printer02	4/24/18
02790a_VEN	EFS	Files Transferred	Printer01	4/30/18
02791a_VEN	Thermal	Rejected		Back To Client
02801a_VEN	EQP
02802a_VEN	EQP
02803a_VEN	EQP
02804a_VEN	EQP

<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522; width:106pt" span="2" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }.xl67 { color: white; font-size: 10pt; text-align: left; }.xl68 { color: black; font-size: 10pt; }.xl69 { font-size: 10pt; text-align: left; }</style>

Requirements:
1. I want to copy the required details from 'OperatorDetails' sheet to 'Dashboard' sheet in a given format:

Job Number	Brand	PrinterName	Date Uploaded
02789a_VEN	EFS	Printer02	4/24/18
02786a_VEN	Thermal	Printer01	4/30/18
02790a_VEN	EFS	Printer01	4/30/18

<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }</style>
As you notice, the data is sorted according to Date uploaded.

Further to add, the data needs to be populated to the Dashboard in real time without disturbing the old data. Means, that every new data needs will append below the next available empty row. i.e.

02788a_VEN	EFS	Printer02	5/2/18
02801a_VEN	EQP	Printer02	5/2/18
02802a_VEN	EQP	Printer02	5/2/18
02803a_VEN	EQP	Printer02	5/2/18

<tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { color: black; font-size: 10pt; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; }.xl67 { font-size: 10pt; text-align: left; }</style>
The data above will append to the old data just after the '02790a_VEN'.

So, if you finally look at the Dashboard sheet, you'll see:

Job Number	Brand	PrinterName	Date Uploaded
02789a_VEN	EFS	Printer02	4/24/18
02786a_VEN	Thermal	Printer01	4/30/18
02790a_VEN	EFS	Printer01	4/30/18
02788a_VEN	EFS	Printer02	5/2/18
02801a_VEN	EQP	Printer02	5/2/18
02802a_VEN	EQP	Printer02	5/2/18
02803a_VEN	EQP	Printer02	5/2/18

<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }.xl67 { color: black; font-size: 10pt; }.xl68 { font-size: 10pt; text-align: left; }</style>
Can you please suggest something…

Thanks in advance.

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​	Job Number	Brand	Status	PrinterName	Date Uploaded
2​	02786a_VEN	Thermal	Files Transferred	Printer01	4/30/2018​
3​	02787a_VEN	EQP	Rejected
4​	02788a_VEN	EFS
5​	02789a_VEN	EFS	Files Transferred	Printer02	4/24/2018​
6​	02790a_VEN	EFS	Files Transferred	Printer01	4/30/2018​
7​	02791a_VEN	Thermal	Rejected		Back To Client
8​	02801a_VEN	EQP
9​	02802a_VEN	EQP
10​	02803a_VEN	EQP
11​	02804a_VEN	EQP
12​
13​	Job Number	Brand	PrinterName	Date Uploaded
14​	02789a_VEN	EFS	Printer02	4/24/2018​
15​	02786a_VEN	Thermal	Printer01	4/30/2018​
16​	02790a_VEN	EFS	Printer01	4/30/2018​
17​

Sheet: Sheet28

Formula in D14:
=SMALL($E$2:$E$11,ROWS($A$1:A1))

Array formula in cell A14:
=INDEX($A$2:$D$11,SMALL(IF($D14=$E$2:$E$11,MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:A1))
Copy cell A14 and paste to the next cell to the right, then copy both cells A14:B14 and paste to cells below as far as needed.

Array formula in cell C14:
=INDEX($A$2:$D$11,SMALL(IF($D14=$E$2:$E$11,MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:D1))
Copy cell C14 and paste to cells below as far as needed.

 

[MASOODAHMAD]

Thanks Oscar, it really worked.

Since my data is in two different files, I changed the references accordingly:

Formula in D14:
=SMALL([v4.xlsx]Printer_Sheet!$F$2:$F5141,ROWS([v4.xlsx]Printer_Sheet!$A$1:A1))

Array Formula in Cell A14:
=INDEX([v4.xlsx]Printer_Sheet!$A$2:$M5141,SMALL(IF($D14=[v4.xlsx]Printer_Sheet!$F$2:$F5141,MATCH(ROW([v4.xlsx]Printer_Sheet!$F$2:$F5141),ROW([v4.xlsx]Printer_Sheet!$F$2:$F5141)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS([v4.xlsx]Printer_Sheet!$A$1:A1))

And so in B14 and C14.

What I figured out now is that it is giving me "#NUM!" error for blank rows. So I need to get rid of these.
Secondly, I want to fetch the data only when the value in C column of my source is equal to "Files Transferred".

The formula worked well on my MS Excel on Mac. I wonder if the same works too on Google sheets?

One again thanks a lot for your time and suggestions.

 

[Oscar Cronquist]

What I figured out now is that it is giving me "#NUM!" error for blank rows. So I need to get rid of these.
Secondly, I want to fetch the data only when the value in C column of my source is equal to "Files Transferred".

Array formula in cell A14:
=IF(ROWS($A$1:A1)>COUNT($E$2:$E$11),"",INDEX($A$2:$D$11,SMALL(IF(($D14=$E$2:$E$11)*($C$2:$C$11="Files Transferred"),MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:A1)))

 

[MASOODAHMAD]

Thanks Oscar, that really worked. And sorry for thanking you so late as I got stuck in some other work.